I have tried to write code to open a workbook (have several that needs to be opened one at a time) and to copy the data from Col Q2:R to sheet1 Col A in the destination workbook. Once the second workbook is opened, it must copy the data to the first blank row after the last row containing data
I have the following code, but comes up with subscript out of range and the code "Sheets("Sheet1").Select" is highlighted
Your assistance in resolving this is most appreciated
I have the following code, but comes up with subscript out of range and the code "Sheets("Sheet1").Select" is highlighted
Your assistance in resolving this is most appreciated
Code:
Sub Open_Workbook()
ChDir ("C:\my Documents")
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Dim nb As Workbook, tw As Workbook, ts As Worksheet
A = Application.GetOpenFilename
If A = False Or IsEmpty(A) Then Exit Sub
With Application
.ScreenUpdating = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(A)
With Sheets("Imported Data")
Range("Q2:R" & Lr).Copy
Sheets("Sheet1").Select
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Selection.Paste
End With
End Sub