VBA Set Workbook Name in Code from Cell Value

[JoyMaree]

I have 2 workbooks open and I am running code from Workbook 1 to copy and paste data into Workbook 2.
The problem is Workbook 2's name changes every month and the Worksheet changes every day.
I can't use Active Workbook as I have 2 open and i am running the Macro from Workbook 1.



Set Claim_Reg = Workbooks("Workbook 2 September 2018.xlsx")
Set Daily = Claim_Reg.Worksheets("13 Sep 2018")
Set Alloc_Details = Workbooks("Workbook 1.xlsm")
Set Alloc = Alloc_Details.Worksheets("Allocation")

I would like to have a cell in the Workbook 1 on a Tab called "Parameters" that I can change every day.

Any help would be appreciated.

 

[=ODIN=]

Maybe this will get you started:

[COLOR=#333333]Set Alloc_Details = Workbooks("Workbook 1.xlsm")
[/COLOR][COLOR=#333333]Set Alloc = Alloc_Details.Worksheets("Allocation")
[/COLOR]Set Params = Alloc_Details.[COLOR=#333333]Worksheets("[/COLOR][COLOR=#333333]Parameters[/COLOR][COLOR=#333333]")
[/COLOR]
[COLOR=#333333]Set Claim_Reg = Workbooks(params.range("b3").value) 'assumes you have the name of this workbook on params sheet in cell b3[/COLOR]
[COLOR=#333333]Set Daily = Claim_Reg.Worksheets(params.range("b4").value) [/COLOR][COLOR=#333333]'assumes you have the name of this worksheet on params sheet in cell b4[/COLOR]

 

[JoyMaree]

Thank you so much. It works perfectly.
I have been searching threads for a couple of hours and couldn't find anything to solve the problem.
Much appreciated.

 

[JoyMaree]

Hi Odin,

Is it possible to have "B3" as a formula?
The code above works if it is text but not if I formulate the sheet name to change each month.

Tks
JoyMaree

 

[=ODIN=]

Hi Joy,

I'm a bit confused. Even if you have a formula in B3 to create a workbook name, the vba would grab whatever that formula resolved to. For example, if B3 has the formula

 ="[COLOR=#333333]Workbook 2"  & " September 2018.xlsx" [[/COLOR]/CODE]  the vba would grab "[COLOR=#333333]Workbook 2 September 2018.xlsx"

B3 must have a valid workbook (not worksheet) name, that is already opened along with workbook 1.


[/COLOR]

 

[JoyMaree]

Hi Odin,

I looked at it again and it's all good.
Thank you for your quick response.

Joy