I am trying to consolidate from multiple Excel workbooks loaded individually into a single master spreadsheet via VBA code.
The code fragment following works if I reference within the workbook(2), but fails if I copy from workbook(2) and paste across to the master workbook(1).
One web reference suggested it was due to separate instances of Excel – that fitted the error, but loading both files from Excel File, Open menu did not fix it.
=========== CODE ===================
ActiveWorkbook.Sheets(2).Range(Cells(ConsultantRow, BegDayCol), Cells(ConsultantRow, EndDayCol)).Select
Selection.Copy ‘Destination:=Workbooks(1).Sheets(2).Cells(ConsultantRow, BegDayCol)
‘ Paste data to MASTER
Workbooks(1).Sheets(2).Paste Destination:=Workbooks(1).Sheets(2).Range(Cells(ConsultantRow, BegDayCol), Cells(ConsultantRow, EndDayCol))
————————–
Run-time error ‘1004’
Application-defined or object-defined error
===================== END CODE ============
I can reference the name of the worksheet(1)from worksheet(2) code, but cannot seem to paste to worksheet(1). What’s up ?
Thanks
Grhys