CODEWITHSUNDEEP
vbaexcel
Scott
Scott

Reputation: 79

Working between multiple active workbooks

Macro 1 code is stored in Workbook 1. Command button 1 triggers opening a user selected workbook. Let's call that Workbook 2 (although the actual Workbook name is different each time). Macro 1 runs codes (mostly formatting commands) in Workbook 2.

Macro 1 ends. Workbook 2 remains open. After some manual intervention by user on Workbook 2, user clicks Command button 2 in Workbook 1, which triggers Macro 2.

Macro 2 starts by activating Workbook 2 again, running code. At one point, code says to Open another user selected Workbook. Let's call it Workbook 3 (again, filename can be different). Then Macro 2 has codes that takes actions back in Workbook 2. Then goes back to Workbook 3.

The problem I am having is I cannot figure out how to Dim each of these workbooks given the Workbook names/WorkSheet names change. Also, with new Workbooks being opened at different points, I am not sure which parts of which Modules I need to add code.

Upvotes: 2

Views: 22292

Answers (1)

VBA Pete
VBA Pete

Reputation: 2666

You can for example do the following to dim books and sheets:

First you need declares the workbooks:

Dim wbBook1 As Workbook
Dim wbBook2 As Workbook

Then you need to set each workbook (note that the second book is being opened while set:

Set wbBook1 = ThisWorkbook
Set wbBook2 = Workbooks.Open("C:\PERSONAL_FOLDER\test.xlsm")

In order to identify which sheets are you working with, you need to declare and set these as well:

Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Set wsSheet1 = wbBook1.Worksheets("Sheet1")
Set wsSheet2 = wbBook2.Worksheets("Sheet1")

Using wsSheet1 and wsSheet2 you can easily run macros that do something in your current workbook in sheet 1 and in workbook test in sheet 1.

Once you are done using workbook test you can close it with the following command:

wbBook2.close

I hope this helps!

Upvotes: 5

Related Questions