VBA code to switch back and forth between 3 different workbooks

[JoeRooney]

Hi,

Wondering if anyone could help me with a problem I have with switching between 3 different open workbooks.

The Workbook names never remain constant.

1st workbook ( Main Workbook that the code is ran from ) name will always change
2nd workbook , name will change but will always have "Test1" included in the name
3rd workbook , name will change but will always have "Test2" included in the name

I need to toggle between each workbook to copy and paste data.

Any help is greatly appreciated

 

[Norie]

How are you opening the 2nd and 3rd workbooks?

 

[JoeRooney]

Hi Norie , The 2nd and 3rd workbooks are already open , they are not opened through the code.

And that's the problem I have been having , I cant figure out how to do it as they are already opened.

 

[JoeRooney]

Hi ,

Just refreshing this post with a comment hoping someone can assist. Would anyone have any suggestions?

Thanks

 

[Jaafar Tribak]

Hi ,

Just refreshing this post with a comment hoping someone can assist. Would anyone have any suggestions?

Thanks

Will there be other workbooks open aside the 3 workbooks ? BTW, you don't need to activate workbooks in order to copy from or paste to them.

It would be useful if you could show us some small code example that illustrates what and how you are copying and pasting

 

[JoeRooney]

Hi , There is the possibility that other workbooks will be open yes but for the macro I will only be using the 3 of them.

So I will be running the macro when Test3 is the active workbook , then I want to copy data (as below) into the Test3 workbook from Test1 & 2.

The problem I am having is that the workbook names for Test1 & 2 will not remain constant bar the Test1 and Test2 piece.

Windows("111111_1111111_Test1.csv").Activate
Selection.Copy
Windows("Test1.xls").Activate
Range("E2601").Select
ActiveSheet.Paste

 

[offthelip]

try something like this (untested)

Sub test()
Dim wbname As String
Dim wb As Workbook
Dim wbs As Workbooks
Set wbs = Application.Workbooks
wbfnd = False
For Each wb In wbs
   wbname = wb.Name
   If InStr(wbname, "Test1") Then
    wb.Activate
    wbfnd = True
   End If
Next wb


End Sub

 

[Norie]

Wouldn't it be easier if you asked the user to select the 2 other workbooks to open via a dialog?

If you did that you could create references to them which could be used throughout the rest of the code.

 

[JoeRooney]

Thanks everyone for their input, I have it working now. Here is my code


Sub BAU_Tool1()
Dim wbname As String
Dim wb As Workbook
Dim wbs As Workbooks
Dim w As Workbook: Set w = ActiveWorkbook

Set wbs = Application.Workbooks
wbfnd = False
For Each wb In wbs
wbname = wb.Name
If InStr(wbname, "DlyWorkings") Then
wb.Activate
wbfnd = True

Range("T2").Select
Selection.Copy
w.Activate
Sheets("Calculation").Select
Range("E2601").Select
ActiveSheet.Paste


End If
Next wb
Call BAU_Tool2
End Sub