Referring to Other Worksheets or Workbooks in an Excel Macro

Editor’s Note: This is the second of two articles focused on Excel macros culled from audience questions asked at last week’s MrExcel Webcast, “Macros for Budgeting & Corporate Accounting.” The first article about using text-to-columns in a macro was the topic of last week’s column.

During last week’s Webcast, I covered one simple macro recording example — and then covered the four things you absolutely have to learn to create effective macros. Basically, the Excel macro recorder will never record these four items for you. However, once you master the quartet of items, you can use a combination of the macro recorder and the four concepts to solve any problem in Excel. The Webcast ran five minutes long, and I did not get to address all the Q&A at the end. (By the way, if you missed the Webcast, an archive will soon be available at CFO.com’s Webcast page.)

So in this column, I will answer the third and final question I didn’t have time to discuss during the online session.

Question 3: Can You Access Information in Another Workbook with a Macro in a Different Workbook?
The answer is yes. When you start to refer to other worksheets and workbooks, you are moving beyond the types of macros that the macro recorder provides and moving on to a powerful stage in your macro building progression.

Before showing how to solve this problem, think about how the macro recorder works. If you look at code generated by the macro recorder, it deals with other worksheets or workbooks by first activating the other workbook, selecting the other worksheet, and then operating on the worksheet.

Workbooks(“Accounting Reports.xlsm”).Activate

Sheets(“Balance Sheet”).Select

Range(“G22”). Select

Selection.Copy

While the macro recorder always selects something before acting upon it, this is not necessary when you are writing VBA code without the macro recorder. You can accomplish all four lines above in a single line of code:

Workbooks(“Accounting Reports.xlsm”). Sheets(“Balance Sheet”).Range(“G22”).Copy

Using Special Variables to Refer to Workbooks or Worksheets
In the Webcast, I talked about using simple variables to hold a single number, such as FinalRow, TotalRow, FirstRow, and so on. There is another type of variable in VBA that is called an Object Variable. An Object Variable can refer to an entire worksheet or a workbook or a chart or any other object in Excel. You should always declare the object variables at the top of the macro. It is not required, but I always begin my workbook object variables with WB and my worksheet object variables with WS.

Imagine a scenario in which the macro will be run while a certain workbook is open. This workbook has a data worksheet and a lookup worksheet. The goal will be to create a brand-new workbook with a single report worksheet. In this scenario, I would use WSD as the variable for the data worksheet, WSR for the report worksheet, and WSL for the lookup worksheet. I would use WBN to refer to the new workbook that is created, and I would use WBT to refer to the original workbook (the T stands for “This Workbook,” the workbook in which the code resides).

To define the object variables, use the DIM statement. You would use five DIM statements to define the five worksheets/workbooks.

Sub MyAmazingMacro()

Dim WBT As Workbook ‘ This Workbook

Dim WBN As Workbook ‘ New workbook

Dim WSD As Worksheet ‘ Data

Dim WSL As Worksheet ‘ Lookup sheet

Dim WSR As Worksheet ‘ Report worksheet, in WBN

Note that the words after the apostrophe in each line are simply a comment to help others understand the macro. The comments do not actually do anything in the macro.

Once you have defined the object variables, you can assign a worksheet or a workbook to the variable using the Set command.

Set WBT = Workbooks(“Raw Data.xlsm”)

Set WSD = WBT.Worksheets(“GLData”)

Set WSL = WBT.Worksheets(“CCtrList”)

Set WBN = Workbooks(“Accounting Reports.xlsm”)

Set WSR = WBN.Worksheets(“Balance Sheet”)

After defining the variables, it becomes simpler to refer to cells in another workbook or worksheet. You can refer to cells in any worksheet without activating the worksheet or workbook, provided the workbook is open in the current Excel session. The following line will copy cell C4 from the CCtrList worksheet in the current workbook out to cell Z9 on the Balance Sheet workbook in the AccountingReports.xlsm worksheet:

WSR.Range(“Z9”) = WSL.Range(“C4”)

Tricks of the Trade
There are a lot of tricks for referring to workbooks and worksheets.

Many times you want the macro to refer to whichever worksheet was active at the time the macro started running. Excel VBA provides a keyword called ActiveSheet to refer to the currently active worksheet. If you define WSD as the ActiveSheet, the macro will remember which worksheet was active at the beginning of the macro. You can then navigate back to that worksheet later.

Sub ReturnToOriginal()

Dim WSO As Worksheet ‘ Original worksheet

Dim WSN As Worksheet ‘ new worksheet created by macro

Set WSO = ActiveSheet

‘…. macro here to create WSN

‘Return back to original worksheet

WSO.Select

End Sub

Excel VBA provides two keywords to refer to workbooks. ActiveWorkbook will refer to the workbook that is currently active in the Excel session. ThisWorkbook refers to the workbook where the macro code resides. Sometimes those are the same workbook, but sometimes they are not. Here is an example.

Every day the IT department generates a single Excel worksheet with all of the open accounts receivable. Every day this workbook arrives with a different name. Today’s file might be called ARLog20110823.xlsx. Tomorrow’s file would be ARLog20110824.xlsx. Rather than changing the macro each day to deal with the name, you can set up a process whereby you open the macro workbook, open the current day’s AR log, and then invoke the macro. You can write the macro assuming that the data is in the ActiveWorkbook and that your lookup worksheet is in the macro workbook.

Sub ProcessTodaysData()

Dim WBT As Workbook ‘ macro workbook

Dim WBD As Workbook ‘ data workbook from I.T.

Dim WBN As Workbook ‘ New workbook, created by macro

Dim WSD As Worksheet ‘ only worksheet in WBD

Dim WSL As Worksheet ‘ Lookups, in WBT

Dim WSR As Worksheet ‘ Report, created in WBN

‘ Note: They should have data worksheet be active before running

Set WBT = ThisWorkbook

Set WBD = ActiveWorkbook

Set WSL = WBT.Worksheets(“CCtrList”)

Set WSD = WBD.Worksheets(1) ‘ Not sure what it is called each day

In the macro above, you get around not knowing the name of the current day’s worksheet by referring to it as ActiveWorkbook. If the worksheet also has a different name each day, using Worksheets(1) to refer to the first (in this case, the only) worksheet in the workbook is an effective trick.

Opening a Workbook
If you record the action of opening a workbook, the macro recorder will produce:

Workbooks.Open FileName:=”C:DataAccountList.xls”

If you want to refer to this workbook with an object variable, you can assume that the opened workbook becomes the active workbook immediately after opening the workbook:

Dim WBA as Workbook ‘ Account List workbook

Dim WSL as Worksheet ‘ Lookup worksheet

Workbooks.Open FileName:=”C:DataAccountList.xls”

Set WBA = ActiveWorkbook

Set WSL = WBA.Worksheets(1)

Alternatively, you can open the workbook and assign it to an object variable in a single line of code. Note that you have to add the parentheses to the Workbooks.Open command:

Dim WBA as Workbook ‘ Account List workbook

Dim WSL as Worksheet ‘ Lookup worksheet

Set WBA = Workbooks.Open(FileName:=”C:DataAccountList.xls”)

Set WSL = WBA.Worksheets(1)

Creating a New Blank Workbook
Many times in a macro, I want to create a completely new workbook. By default, a new workbook in Excel will have three worksheets. However, many savvy Excellers have changed the default to only put a single worksheet in their new workbooks. Someone using Excel 97 might automatically get 16 worksheets in their new workbooks. Rather than leave this to chance, I use a built-in template called xlWBATWorksheet to generate a new workbook with exactly one worksheet:

Sub AddAWorkbook()

Dim WBT As Workbook ‘ Original workbook

Dim WBN As Workbook ‘ New workbook

Set WBT = ActiveWorkbook

Workbooks.Add xlWBATWorksheet

Set WBN = ActiveWorkbook ‘ the new workbook

WBT.Activate ‘ Go back to original workbook

Just as in the previous example, you can create the new workbook and assign it to an object variable in a single line of code. Again, add the parentheses:

Sub AddAWorkbook()

Dim WBT As Workbook ‘ Original workbook

Dim WBN As Workbook ‘ New workbook

Set WBT = ActiveWorkbook

Set WBN = Workbooks.Add(xlWBATWorksheet)

WBT.Activate ‘ Go back to original workbook

Creating a New Blank Worksheet in the Active Workbook
When you use the Worksheets.Add command, Excel will add a worksheet to the current workbook. The worksheet will have an unpredictable name (i.e., Sheet4, Sheet6, Sheet17). You probably will want to rename the worksheet. When you add a worksheet, the new sheet is selected, so you can use the ActiveSheet keyword to refer to the worksheet after adding it.

Sub AddWorksheet()

Dim WSN As Worksheet

Worksheets.Add after:=ActiveSheet

Set WSN = ActiveSheet

WSN.Name = “Report”

Alternatively, you can create the worksheet and assign it to the object variable in one line:

Sub AddWorksheet2()

Dim WSN As Worksheet

Set WSN = Worksheets.Add(after:=ActiveSheet)

WSN.Name = “Report”

Last Trick: Copying a Worksheet to a New Blank Workbook
Rather than creating a workbook with a single blank worksheet and then populating that workbook with data, you could add a worksheet to the active workbook, populate the worksheet, then copy the finished report to a new workbook.

Simply using the .Copy command on a worksheet will copy that worksheet to a new workbook. The new workbook becomes active, so you can refer to the workbook using ActiveWorkbook

Sub CreateReportWorkbook()

Dim WBT As Workbook ‘ This workbook

Dim WBR As Workbook ‘ New workbook created later

Dim WSN As Worksheet ‘ New worksheet

Set WSN = Worksheets.Add(after:=ActiveSheet)

WSN.Name = “Report”

‘ Lots of code here to generate report

‘ When the report is done, copy to new workbook

WSN.Copy

Set WBR = ActiveWorkbook

WBR.SaveAs “C:ReportsReport.xlsx”

End Sub

The examples here will give you a starting point for referring to other worksheets and workbooks in your macros.

Bill Jelen is the author of 33 books about Microsoft Excel and the host of MrExcel.com. Submit questions to be considered for future articles by clicking on his byline at the top of this article and sending him an e-mail message or by sending a note directly to [email protected].