Reference a workbook based on a cell value

[AccountantHarry]

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:

   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

Regards

 

[ranman256]

you defined sourceWb (and targetWB) as WORKBOOKS, yet you assigned a VALUE to them.
you should assign workbooks to workbooks:
set sourceWb = Thisworkbook
or
set sourceWb = workbooks("wbName")

 

[AccountantHarry]

Thanks, I will have a go. Happy New Year!