Hi,
I've been trying to get this to work for ages now and I can't get my head arount it but I feel like it should be doable.
I want to open another workbook from which I get data, paste it into the current workbook, then modify and paste into yet another one.
However these other workbooks have filenames that may change and I want to input the filename that interests me e.g. in cell C4.
I would love to cut down on the code and not have to use Workbooks.Activate each time...
Current code excerpt:
(...)
ThisWorkbook.Sheets("Sheet1").Range("A5").Copy
Workbooks("D:\Test\" & ThisWorkbook.Sheets("Sheet1").Range("C4") & ".xls").Activate
ws.Range("B" & ws.Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Paste
The code that fails:
(...)
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("D:\Test\" & ThisWorkbook.Sheets("Sheet1").Range("C4") & ".xls")
Set ws = wb.Sheets("Test")
ThisWorkbook.Sheets("Sheet1").Range("A5").Copy
ws.Range("B" & ws.Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Paste
It gives the "subscript out of range" error each time on the "Set wb" row.
I wonder if this is possible and if so, what am I doing wrong...
Cheers and all the most Excel-lent in 2016
Cloudberr
Bookmarks