Activate a dynamically named workbook

[TBRoberts]

Hello all thank you for reviewing my question.

I have a report.xls that is pulled from a database and dynamically named given the value in cells A2, B2 and C2. (Ex: .Range("A2").value & "_" & .Range("B2").value & "_" & .Range("C2").value). I am attempting to move two additional workbooks to the master workbook with VBA. I would like to be able to activate the master workbook from any open workbook and combine them all into the master.

Thanks for the advice!

 

[StephenCrump]

Welcome to the Forum!

Sorry, it's not totally clear what you are doing here ....

Where is the code that is dynamically saving the report. Is it in the "master workbook"? It would probably help if you could post this code?

What are the two "additional workbooks"? Can we assume that these are all the other workbooks already open? Or do they need to be located and opened?

What exactly do you mean by "combine"? Copy sheets? Match unique identifiers and add/update fields?

 

[TBRoberts]

I am to export 3 excel workbooks from a database, which are all named based on the material located within various cells of the first sheet in their respective workbooks. I would like to combine all of the workbooks into one. Workbook 1 (the master), is named (not saved) given the values in cells A2:C2. The kicker, is that I would like to be able to run this with more than the 3 necessary workbooks open at the same time. I know that the fist tab of the master workbook will always be called "summary", so this is what i have so far:

-None of the workbooks are saved. They are simply just exported.
-I do not know where the code is that is dynamically saving the report. I do not think that it is done in excel, it is built into the database, I assume. However, I am not an experienced developer, so I truly do not know.
-All of the needed workbooks are already open. However, there could me more irrelevant workbooks open at this time.
-The non-master workbooks consist only of one sheet. I simply just need to move the first sheet of each of the 2nd and 3rd workbooks into the master workbook (at the end).


Sub CombineWorkbooks()
'Find the "Master Workbook"
Dim WB As Workbook

For Each WB In Workbooks
WB.Activate
If ActiveSheet.Name = "Summary" Then
Range("a1").Select 'This is the only way that i could figure out how to make the loop stop once it found the master workbook. - Any suggestions on other ways to do this would be awesome.
Else
End If
Next WB
'Retreive the dynamic name of the "Master" workbook
Master = ActiveWorkbook.Name

'Now, I could write another macro, which would reference the name of the master and combine.
However, I would like to be able to do this all in one if possible. As stated above, I would like for this to run, even if there are additional workbooks open.

Thank you for your quick response and please let me know if you need anything else!

 

[StephenCrump]

Perhaps something like this:

Sub CombineWorkbooks()

    Dim wbMaster As Workbook, wbToCopy As Workbook, wb As Workbook
    
    For Each wb In Workbooks
        With wb.Worksheets(1)
            If wb.Name Like .Range("A2").Value & " " & .Range("B2").Value & " " & .Range("C2").Value & "*" Then
                Set wbMaster = wb
                Exit For
            End If
        End With
    Next wb

    'Set wbToCopy = ?     'How do we locate?
    'wbToCopy.Worksheets(1).Copy After:=wbMaster.Worksheets(wbMaster.Worksheets.Count)
    
End Sub

This identifies the master workbook based on the contents of A2, B2 and C2 in the first sheet (perhaps a better test than simply looking for a sheet called "Summary"?)

What's the best way to identify wbToCopy (times 2)?

 

[TBRoberts]

Okay, so I wasn't familiar with the Like operation before so thank you lol. I wasn't able to get your exact code to locate the master workbook. However, after some research, I came up with this (the master workbook name will always end with "Customer_Information" so the Like function was very nice, and changed the way I searched a bit):

Dim Master As Workbook, WB As Workbook, WbToCopy As Workbook

For Each WB In Workbooks
WB.Activate
If WB.Name Like "?*Customer_Information*" Then
Set Master = ActiveWorkbook
Exit For
End If
Next WB


This works. It will find my master workbook and activate it. I can also call on it by using master.activate within the immediate window. However, now I am having issues calling on the wbToCopy. I am using Data1 as the name of wbToCopy for now as a mock title. This is my entire code. The first for loop works, the second does not.

Sub CombineWorkbooks()

Dim Master As Workbook, WB As Workbook, wbToCopy As Workbook

For Each WB In Workbooks
WB.Activate
If WB.Name Like "?*Customer_Information*" Then
Set Master = ActiveWorkbook
Exit For
End If
Next WB

For Each WB In Workbooks
WB.Activate
If WB.Name Like "?*Data*" Then
Set wbToCopy = ActiveWorkbook
Exit For
End If
Next WB
End Sub

 

[StephenCrump]

"Data1" is not Like "?*Data*" because the "?" looks for a single character.

But "Data1" is Like "*Data1*" because "*" represents 0 or more characters.

 

[TBRoberts]

Yessssssss. I think I can take it from here. Thank you !!