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.

The Future of Finance Has Arrived

The pace with which finance functions are employing automation and advanced technologies is quickening. Rapidly. A new survey of senior finance executives by Grant Thornton and CFO Research revealed that, for just about every key finance discipline, the use of advanced technologies has increased dramatically in the past 12 months.

Read More

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

‘ Lots of code here to generate 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 BillJelen@cfo.com.

, , , , , , , , ,

9 responses to “Referring to Other Worksheets or Workbooks in an Excel Macro”

  1. I have written the following Code according to your above post please help. I am attempting to refrence cell B8 in one workbook and populate cell B5 in a second workbook. Is the belwo code done correctly?
    Sub Propertylist()
    Dim WBT As Workbook ‘This Workbook
    Dim WBN As Workbook ‘New Workbook
    Dim WSD As Worksheet ‘Properties Lisy Data Entry
    Dim WSL As Worksheet ‘Master Data Sheet
    Set WBN = Workbooks(“RA2 Databook 2Q14 Linked CR.xlsm”)
    Set WBT = Workbooks(“Deutsche RREEF America REIT II-2014 Q2 Questionnaire.xlsm”)
    Set WSD = WBT.Worksheets(“Properties List Data Entry”)
    Set WSL = WBN.Worksheets(“Master Data Sheet”)
    WSL.Range(“B8”) = WSD.Range(“B5”)
    End Sub

  2. Thank you for sharing your wisdom! I LOVE Excel and I LOVE Macros. It is so fun to be learning new commands. Here is one command that I like to use as well:

    I use this when the workbook that I am coping data into changes weekly or monthly. I just update cell F1 in my excel file that contains the macro and the magic happens.

    ‘Establish GLDownload file name ie. GLDownload20150131.xlsx
    Dim GLDownload As String
    GLDownload = WBT.Worksheets(“Sheet1”).Range(“F1”).Value

    I also use this next one to have the macro recognize which worksheet it should go into.

    ‘Establish GLDownload worksheet name
    Dim Wksht As String
    Wksht = WBT.Worksheets(“Sheet1”).Range(“I1”).Value

    But, I just realized yesterday that the reason my this second command never worked right is because I didn’t place it in the correct spot of the macro (order of operation). Part of my macro copied the account number from the IT Dept report to my macro file to establish the worksheet name; however, first I put this command above the copy and paste command so it was using incorrect information from what was first in the cell. Once I changed the location of this second command the macro worked perfectly. So exciting!

    Please let me know what you think about this command. You might have a better idea!

  3. Hi,
    I just started to learn VBA and wanted to transfer the data from File A to File B this is the Code below ..I ran the Code in Sheet 1 Modules and whenever I try to run the Code I get a message that the File A is already opened and reopening will cause the changes to be discarded…Can you please let me know if iam doing this correctly… and do we need to run the code in the Sheets or Modules or This Workbook?
    Sub Add_Fn()
    Dim FileA As Workbook
    Set FileA = Workbooks.Open(“C:\Users\SM00499998\Desktop\FileA.xlsx”)
    ThisWorkbook.Sheets(3).Cells(1, 1) = FileA.Sheets(1).Cells(1, 1)
    FileA.Save
    FileA.Close
    End Sub

  4. How can you input the name of a worksheet that you will use later in a sub? For example I tried this to use in a Vlookup but it did not work:

    Dim WSL As Worksheet
    Set WSL = WBT.Worksheets(InputBox(“Worksheet:”))

    ‘Vlookups
    ActiveSheet.Range(“I5”).Select
    ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-6],’WSL’!R5C3:R264C12,7,FALSE)”

  5. Really, you are Mr. Excel who can be the guide for myriad learners of VBA. Thank you so much for your article “Referring to Other Worksheets or Workbooks in an Excel Macro”. It is like a glass of water to a thirsty person like me on this stage. Keep on imparting knowledge to the world.
    I want more about how we can refer to another workbook using variable which gets its values from a worksheet cell.

  6. Dear sir, I also tried the same but only on some occasions it works and I am unable to find why. Mostly when I try to reference a worksheet after opening a workbook (mostly accepted when object variable is used for workbook to open ) but worksheet object variable does not work when i try to copy data from the worksheet of the same workbook. I have a personal macro workbook with all the codes stored there and want to get data from a closed workbook sheets into an array variable. How can i solve this please?

  7. Thanks for your posts – I really enjoy learning VBA and macros etc…I’m fairly new / novice. Here is what I’m attempting to do, any help / direction is appreciated.

    1. Open file with macro and formulas called ‘TODAYSDATE NINJA’ [check I got this one down]
    2. My PM software generates a daily excel file. It opens generally called “All – Excel” but is sometimes “All – Excel(2)” ….somewhat varying. [check I got this]
    3. My material management software generates an excel file. I name it “TODAYSDATE BL”. [check I got this]
    4. Here’s where I need help. I want to copy data from “All …” and “TODAYSDATE BL” into the ‘TODAYSDATE NINJA’ file. All three open workbooks have somewhat of variable file names, creating my problem.
    Again, any direction is appreciated – and if possible, dumb it down for me….I’m still learning this stuff! Ha. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *