Hi all,
I want to copy values from Workbook B to Workbook A but only the rows in the range without a blank cell in column U in workbook B (S2:U5) in my code.
I would like the range to not be defined, but rather to the last row in column U. Then paste these values to Workbook A starting from cell L5.
------------------------------------------------------------------------------------------------ Code 1 (works without dynamic range)
Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B
Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U5").Copy 'This is my file location for Workbook B
ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues
----------------------------------------------------------------------------------------------- Code 2 (my attempt but doesn't work)
Sub Timestamp()
Workbooks.Open ("Workbook B") 'This is my file location for Workbook B
Dim erow As Long, lastrow As Long, i As Long
lastrow = Timestamp.Cells(Rows.Count, 21).End(xlUp).Row
For i = 2 To lastrow
If Timestamp.Cells(i, 21) <> "" Then
Sheets("Timestamp").Range(Cells(i, 19), Cells(i, 21)).Copy
ThisWorkbook.Sheets("Toyota").Activate
ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues
End If
Next i
End Sub
I want to copy values from Workbook B to Workbook A but only the rows in the range without a blank cell in column U in workbook B (S2:U5) in my code.
I would like the range to not be defined, but rather to the last row in column U. Then paste these values to Workbook A starting from cell L5.
------------------------------------------------------------------------------------------------ Code 1 (works without dynamic range)
Workbooks.Open ("Workbook B.xlsm") 'This is my file location for Workbook B
Workbooks("Workbook B.xlsm").Sheets("Timestamp").Range("S2:U5").Copy 'This is my file location for Workbook B
ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues
----------------------------------------------------------------------------------------------- Code 2 (my attempt but doesn't work)
Sub Timestamp()
Workbooks.Open ("Workbook B") 'This is my file location for Workbook B
Dim erow As Long, lastrow As Long, i As Long
lastrow = Timestamp.Cells(Rows.Count, 21).End(xlUp).Row
For i = 2 To lastrow
If Timestamp.Cells(i, 21) <> "" Then
Sheets("Timestamp").Range(Cells(i, 19), Cells(i, 21)).Copy
ThisWorkbook.Sheets("Toyota").Activate
ThisWorkbook.Sheets("Toyota").Range("L5").PasteSpecial Paste:=xlPasteValues
End If
Next i
End Sub