Assign Open Workbook To A Variable Based On A Condition

[picton2000]

I have this code at the start of my VBA.

Sub Starting_Workbook()

Dim x As Workbook, y As Workbook

Set x = Workbooks(1)
Set y = Workbooks(2)

End Sub

I have a macro workbook looking at two open workbooks where the workbook name is always variable.
On one of the workbooks cell E1 always has the text "Wage Code".

Depending on the order the user opens the workbooks or which one is active when the macro is executed I cant guarantee I will get the result I expect.

If some extra code can be added so the workbook which contains "Wage Code" in E1 is always set as variable y that would solve my problem.

Any help would be appreciated.

Thank you

[Richard Buttrey]

Hi

Why not use the workbbok name. i.e.

Set y = Workbooks("TheWorkbookName.xlsm")

[picton2000]

Hello Richard,

Your suggestion won't assist me because the workbook names are variable.

Ideally what I'm looking for is code that will loop through open workbooks and checking cell E1 each time.
When it finds a workbook that has "Wage Code" as the text in cell E1 it sets that workbook as variable y.

[Richard Buttrey]

Are there several sheets in each workbook and in which case which sheet is the E1 value you want to test? If more than one and assuming the sheet vb codename is always the same - (unlikely it isn't_ then perhaps something like

Please Login or Register  to view this content.

[Mumps1]

Maybe:

Please Login or Register  to view this content.

The code assumes you want to search for "Wage Code" in Sheet1 of each workbook.

[picton2000]

Thank you Richard, that is exactly what I needed.

Regards
Jonathan Williams

[picton2000]

Thank you Mumps1 your code works for me.

Regards
Jonathan Williams

[Mumps1]

Glad to help.

[picton2000]

Hello Richard,

Apologies for coming back to you on some code you gave where I closed the thread.
Is there a way so your third line of code does refer to sheet 1, but the actual name of the first sheet can be variable.
I am finding your code works as long as the first sheet in the workbook is called Sheet1.

Thanks for your help.
Jonathan

[picton2000]

Hello Richard, if you saw my post earlier today, you can ignore it.

With some trial and error I have determined that if you change the third line of code to:

If WB.Worksheets(1).Range("E1") = "Wage Code" Then Set y = wb

Then the code will work no matter what sheet 1 is actually called.