Selected Answer
No sir. Your code doesn't work. Here is what it does.
- It looks at every cell in ther range B3:B50
- If that cell has the value "9/17/2018"
it copies the range B4:L50 to the clipboard
- It then pastes that range - all 46 rows of it to another sheet
starting at A1 of that sheet.
- 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