Macro to Copy specific columns from one workbook to another based on a condition

Do a search on here for this topic and you will find a lot of results. But, basically, you do something like this:

' Reference another workbook
Set Wb1 = Workbooks.Open("C:\sample.xls")

' Paste the data
Wb1.Sheets("Sheet Name").Range("A1").Paste

Here are some helpful links:

TeachExcel Resources

Macro to get Data from Another Workbook in Excel

No sir. Your code doesn't work. Here is what it does.

1. It looks at every cell in ther range B3:B50
2. If that  cell has the value "9/17/2018"
   it copies the range B4:L50 to the clipboard
3. It then pastes that range - all 46 rows of it to another sheet
   starting at A1 of that sheet.
4. It will do that 47 times (once for each cell in B3:B50) if the date is found there

Obviously, only once should be quite enough. You can achieve that by adding the code Exit For as the last row within the If ... End If section. Your code would then look, in turn, at every cell in B3:B50 and if one of them has the specified date in it B4:L50 would be copied to the other sheet. - Probably not very logical but, at least, not repetitive. Perhaps you mean to copy cells only from one row. That would be the subject of another question: how to specify a range address with variable rows.

At the level at which you are now programming you should try to understand objects. Excel's most important object is the Selection object. It is the range you have selected. It can be one cell or many and it needs user action. VBA, however, is designed to act on the user's behalf. The simple fact is that, since the Selection object is always a range, VBA can deal directly with the range - the Range object - without the need of creating a Selection object. Here is a demonstration of the effect.

            ' assigns the Range object "B4:L50" to a new Selection object
            Range("B4:L50").Select
            ' copies the Selection object instead of the Range object
            Selection.Copy

            ' The above is equivalent to
            Range("B4:L50").Copy

Other objects are the Workbook and the Worksheet objects. The macro recorder uses the same roundabout way to translate your keystrokes into functioning code. That's because the recorder can't know what you have in mind until after you press a key. You don't have a similar disadvantage when you write code yourself.

            ' Here the Select method is used to create an ActiveSheet
            '   (Why not use the Activate method to be more precise?
            '    Sheets("Sheet 1").Activate would be clearer.)
            Sheets("Sheet 1").Select

            ' Then the ActiveSheet is used to paste to
            ActiveSheet.Paste


            ' Logically, the above is equivalent to
            Sheets("Sheet 1").Paste

Unfortunately, the above logical construct doesn't work in this case because the Paste command needs an ActiveCell. The ActiveCell is always a part of the Selection object. Your code creates a Selection object comprising of only the ActiveCell with Range("A1").Select.

VBA provides a one-liner to do the entire job by avoiding both the (two) Selection objects, the (two) ActiveSheet objects and the ActiveCell object.

Worksheets("HS").Range("B4:B50").Copy Destination:=Worksheets("Sheet 1").Range("A1")

Observe that the ActiveSheet is still the sheet which was active before the code ran, the Selection is still the selection the user made before the code ran and no cell was activated. In a nutshell, VBA doesn't interfere in the user's work.

Best practise suggests that you declare the workbooks and worksheets you intend to use as variables. Instead of activating this sheet or that, as you would do when clicking with the mouse, you assign each workbook to a variable.

    Dim WbSource As Workbook, WbTarget As Workbook
    Dim WsSource As Worksheet, WsTarget As Worksheet

    Set WbSource = Workbooks("My Data Source.xlsx")     ' must be open
    Set WbTarget = ThisWorkbook

    Set WsSource = WbSource.Worksheets("HS")
    Set WsTarget = TbTarget.Worksheets("Sheet 1")

You might simplify the above by avoiding the explicit declaration of the Target workbook.

    Dim WbSource As Workbook
    Dim WsSource As Worksheet, WsTarget As Worksheet

    Set WbSource = Workbooks("My Data Source.xlsx")     ' must be open

    Set WsSource = WbSource.Worksheets("HS")
    Set WsTarget = ThisWorkbook.Worksheets("Sheet 1")

Either way, you can now write your code much easier than was possible while you "saved" all the time spent on identifying your objects.

    Dim Cell As Range

    For Each Cell In WsSource.Range("B3:B50")
        If Cell.Value = "9/17/2018" Then
            WsSource.Range("B4:L50").Copy Destination:=WsTarget.Cells(1, "A")
            ' Destination:=WsTarget.Range("A1") also works but looks odd in VBA
           ' I would use Destination:=WsTarget.Cells(1, 1)
            Exit For
        End If
    Next Cell          ' Move forward to the next cell