AccountantHarry
New Member
- Joined
- Oct 13, 2019
- Messages
- 18
Hi Experts
I have learnt and gained so much from this forum on VBA, hope someone will be so kind to help me again.
I have written a macro to copy data from one workbook to another (with multiple worksheets), the names for the target and source workbooks are different each month, eg Nov is V11 Sales, Dec is V12 Sales... I have a worksheet called Macro data where I have all the file paths with formulas, so users can change the month, the file paths are then changed automatically. The codes I have written can open the workbooks based on the cells in the Macro data worksheet, however, once the workbooks are opened, I can't seem to be able to get the codes to go back to the workbooks again, the error message is "Run-time error 91, Object variable or With block variable not set".
These are my codes excluding the opening workbooks codes and other stuff:
Regards
I have learnt and gained so much from this forum on VBA, hope someone will be so kind to help me again.
I have written a macro to copy data from one workbook to another (with multiple worksheets), the names for the target and source workbooks are different each month, eg Nov is V11 Sales, Dec is V12 Sales... I have a worksheet called Macro data where I have all the file paths with formulas, so users can change the month, the file paths are then changed automatically. The codes I have written can open the workbooks based on the cells in the Macro data worksheet, however, once the workbooks are opened, I can't seem to be able to get the codes to go back to the workbooks again, the error message is "Run-time error 91, Object variable or With block variable not set".
These are my codes excluding the opening workbooks codes and other stuff:
VBA Code:
Sub Sales()
Dim sourceWb as Workbook
Dim targetWb as Workbook
sourceWb = Thisworkbook.Worksheets("Macro data").Range("B3").Value
targetWb = Thisworkbook.Worksheets("Macro data").Range("B4").Value
Windows("targetWb").Activate
Worksheets("For Macro").Cells(26.11).PasteSpecialxlPasteValues
Attachments
Last edited by a moderator: