Working between multiple active workbooks

Question

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.

How are you activating/using the workbooks/worksheets? Are you using .Select and .Activate to go between them? You can create workbook/worksheet variables to store that info...e.g. Dim dataWB as Workbook // Dim mainDataWS as Worksheet and go from there. What have you tried so far? The workbook name changing can be easily dealt with, but we'd need a little more insight to how your macros work. If you can post the relevant parts of your code, it would help. — BruceWayne
– BruceWayne, Commented Jan 18, 2017 at 23:10
I am using .Select and .Activate I am confused on if I can Dim workbooks and worksheets before they are opened. And if I should place these codes in the Module or in the Worksheet of Workbook 1 where I have the Command Button sub. Dim wbk As Workbooks Set wb2 = Active.wbk '(Workbook 2) that is already open '~~> Get the File MsgBox "Locate MICROSTRATEGY REPORT" Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _ , "Please select file") If Ret1 = False Then Exit Sub wbk.Open (Ret1) '(workbook 3)' wb2.Activate — Scott
– Scott, Commented Jan 18, 2017 at 23:25

VBA Pete · Accepted Answer · 2017-01-18 23:18:05Z

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!

Hi Pete. Thank you, I will certain give it a try. Should this be placed in the Command Button Click sub before it calls the macro, or does it have to be in the module itself?
I would place this in module itself, so each macro can be run indecently without using the button if required.
That worked beautifully. I know these kind of things have been mentioned a zillion times, I just couldn't figure out how to apply it to my own needs. THANK YOU!

Collectives™ on Stack Overflow

Working between multiple active workbooks

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related