Creating VBA Macros to Manipulate Worksheets in Excel 2007

Article
05/23/2014

Summary: Learn how to write Visual Basic for Applications (VBA) macros in Microsoft Office Excel 2007 to automatically name and sort worksheets. In addition, learn how to create and name sets of worksheets based on data in the current workbook, and then save each new worksheet as a new workbook. (12 printed pages)

Mark Roberts, Microsoft Corporation

April 2009

Applies to: Microsoft Office Excel 2007

Contents

Overview
Automatically Naming and Sorting Worksheets
Automatically Creating Workbook Files from Worksheet Data
Conclusion
Additional Resources

Overview

This article describes two scenarios that use VBA code to automate processes that manipulate worksheets in Microsoft Office Excel 2007.

In the first scenario, the developer wants each user to add a worksheet to the workbook and type a name for the worksheet in a specific cell. The developer writes code that automatically names the current worksheet by using the specified value and then sorts all of the worksheets in the workbook by name.
In the second scenario, the developer wants the user to type a list of region names in a column on a worksheet, plus additional information about each region. The developer writes a macro that copies that information and creates a new workbook file for each region.

Important
To access the commands that you need to create VBA code, the Developer tab must be visible in the Excel 2007 Ribbon user interface. If the Developer tab is not visible, use the following steps to display it: Click the Office Button, and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

To access the commands that you need to create VBA code, the Developer tab must be visible in the Excel 2007 Ribbon user interface. If the Developer tab is not visible, use the following steps to display it:

Click the Office Button, and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Automatically Naming and Sorting Worksheets

In this scenario, the developer wants each user to add a worksheet to the workbook, and then to have Excel automatically name and sort all of the worksheets in the workbook. If you want Excel to perform these actions automatically after a user enters the name of the new worksheet in a cell, you must write your VBA code in an event-handler subroutine. An event-handler subroutine is code that runs when the user performs a particular action in Excel, such as opening or saving a workbook, or entering values into worksheets. In the Excel VBA object model, each user action that can have code associated with it is called an event.

Tip
If you search the Excel Developer Reference for "event," you can view all the topics that describe the events that have associated VBA event-handler code. Use the following steps to perform the search: Click the Microsoft Office Excel Help button. Click the drop-down arrow next to the Search button. Click Developer Reference, type "event" in the search box, and then click Search.

If you search the Excel Developer Reference for "event," you can view all the topics that describe the events that have associated VBA event-handler code. Use the following steps to perform the search:

Click the Microsoft Office Excel Help button.
Click the drop-down arrow next to the Search button.
Click Developer Reference, type "event" in the search box, and then click Search.

The following sections describe how to decide which event to use for the event-handler subroutine for this scenario, and how to write the VBA code to name and sort worksheets.

Choosing the Right Event

To create the code for this scenario, you must write the VBA code directly in the Visual Basic Editor rather than record a macro. To open the Visual Basic Editor, on the Developer tab, click Visual Basic. You can start adding code by double-clicking one of the default worksheet names, such as Sheet1, or by double-clicking the ThisWorkbook module in the Project Explorer pane of the Visual Basic Editor. Figure 1 shows the Visual Basic Editor window with the Project Explorer pane in the upper-left corner.

Figure 1. Visual Basic Editor window

Visual Basic Editor window

Initially, you might think that you should add your VBA code to one of the worksheets, and write the event handler subroutine for the Change event of the Worksheet object. If you do that, your code will run whenever the data in the specified target cell is changed, but it will run only when the user adds the name to the cell in that particular worksheet. To create an event-handler subroutine that will run for every worksheet in the workbook (including new worksheets), you must add your code to the ThisWorkbook module and use the SheetChange event of the Workbook object instead. Because the SheetChange event is associated with the Workbook object, an event handler associated with that event can be used to work with any worksheet in the entire workbook. To create an event handler for the SheetChange event, use the following procedure.

To add an event handler to the ThisWorkbook module

On the Developer tab, click Visual Basic to open the Visual Basic Editor.
In the Project Explorer, double-click ThisWorkbook.
In the Object drop-down list (the list above the editing window on the left side that says (General)), select Workbook.

This automatically creates an event handler for the Open event; you must create an event handler for the SheetChange event instead.
In the Procedure drop-down list (the list above the editing window on the right side that now says Open), select SheetChange.

The Visual Basic Editor window should now look like Figure 2.

Figure 2. Visual Basic Editor window with empty Worksheet_SheetChange event handler

Visual Basic Editor with SheetChange event handler

The following sections describe how to write the VBA code for the SheetChange event handler.

Writing Code to Name a Worksheet

In this scenario, you want the user to type a name in a particular cell, and then use that value to automatically name the current worksheet. The property you use to set the name of a worksheet is the Name property of the Worksheet object.

If you record a macro and name a worksheet, and then open the macro code in the Visual Basic Editor, you will see something like the following code.

Sub Macro1()
   Sheets("Sheet1").Select
   Sheets("Sheet1").Name = "MySheet"
End Sub

This code selects and names a particular sheet ("Sheet1") in the workbook Sheets collection, but the goal of this scenario is to name the active worksheet. To access the properties and methods associated with current worksheet from VBA code, you use the ActiveSheet property of the Workbook object. Additionally, the code that the macro generates is hard-coded to use the name that you typed while recording it ("MySheet" in the previous example), and this scenario will retrieve the name from the cell where the user typed that value. To specify a particular cell in the code, use the Range object. To retrieve the value from that cell, use the Value property of the Range object. For example, to assign the value that the user entered in cell B3 as the name of the worksheet, use the following code.

ActiveSheet.Name = Range("B3").Value

Now, you need to determine how to get the event handler for the SheetChange event to run this line of code when a user types a value into cell B3. The SheetChange event provides two parameters: the Sh parameter that provides a Worksheet object that represents the current worksheet, and the Target parameter that provides a Range object that specifies which range on the current worksheet has changed. To complete this part of the code, use the Target parameter in an If statement to conditionally run the code only when the value in cell B3 is changed, as shown in the following example.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Target = Range("B3") Then
      ActiveSheet.Name = Range("B3").Value
   End If
End Sub

At this point, the code will assign the value entered in cell B3 as the name of the worksheet. However, Excel will display the worksheets in the order that they were added to the workbook, so now, you must determine how to automatically sort the names of the worksheets in alphabetical order.

Writing Code to Sort the Worksheets

The code required to alphabetize the names of the worksheets consists of two nested For Each...Next statements that loop through the Worksheets collection. As it loops, the code compares the names of each worksheet, and swaps the worksheets, when necessary, by using the Move method of the Worksheet object. In computer science, this kind of sorting algorithm is called a bubble sort. It is not the most efficient way to sort values, but because this scenario does not sort a large number of items, it will work fine.

To organize the code in the VBA project, the sorting code is in its own subroutine named SortSheets that can be called from the code in the SheetChange event handler. To create a new subroutine in the Visual Basic Editor, type Public Sub followed by the name of the subroutine, and then press ENTER. The code for SortSheets subroutine looks like the following code example.

Public Sub SortSheets()

Dim currentUpdating As Boolean
currentUpdating = Application.ScreenUpdating

Application.ScreenUpdating = False

For Each xlSheet In ActiveWorkbook.Worksheets
   For Each xlSheet2 In ActiveWorkbook.Worksheets
      If LCase(xlSheet2.Name) < LCase(xlSheet.Name) Then
         xlSheet2.Move before:=xlSheet
      End If
   Next xlSheet2
Next xlSheet

Application.ScreenUpdating = currentUpdating

End Sub

Notice that the condition for the If statement that performs the comparison of worksheet names in the inner For Each...Next loop converts both worksheet names to lowercase by using the VBA LCase function.

If LCase(xlSheet2.Name) < LCase(xlSheet.Name) Then

This is done because the comparison operation is case-sensitive. Failing to convert the names to the same case will cause strings with uppercase characters such as "Tom" or "TOM" to be sorted before "harry."

By default, Excel VBA code updates the screen every time it makes a change. Because the sorting code makes multiple passes to sort each worksheet, the screen will flash as the code compares and moves each worksheet. This flashing can be distracting to users and slow down the code as users add more worksheets to the workbook. To eliminate the flashing and speed up execution of the code, the ScreenUpdating property of the Application object is set to False before the sorting operation is started. After the sorting operation is completed, the ScreenUpdating property is set back to the value stored in the currentUpdating variable to restore screen updating to the state it was in when the SortSheets subroutine was called.

To pull all of this code together, modify the original SheetChange event handler to call the SortSheets subroutine after the name of a new or changed worksheet is assigned. The final code for this scenario looks like the following code example.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Target = Range("B3") Then
      ActiveSheet.Name = Range("B3").Value
      Call SortSheets
      Worksheets(Sh.Name).Activate
   End If
End Sub

The new line of code Call SortSheets calls the SortSheets subroutine to automatically sort the worksheets after the name or the worksheet is assigned or changed. The line of code following that, Worksheets(Sh.Name).Activate, calls the Activate method of the Worksheet object to ensure that the worksheet that was current at the start of the sorting operation is active when sorting is completed.

Automatically Creating Workbook Files from Worksheet Data

In this scenario, the spreadsheet developer wants to copy a list of region names in a column on a master worksheet, including additional information about each region, and then run a macro that creates a new workbook file for each region. The coding strategy is to create a new worksheet in the current workbook for each region on the master worksheet, and then to save each of those worksheets as a workbook file.

The code for this solution assumes the following:

The master worksheet that contains the list of region names is named REGION SHEET.
The first three rows of the master worksheet contain boilerplate information that will be copied to each new workbook.
The list of region names is in column B of the master worksheet, and the first name in the list is in cell B4.
The row for each region contains additional information that will be copied to the new workbook for that region.

A simplified example of the data in the REGION SHEET worksheet looks like the following table.

Figure 3. Sample REGION SHEET data

Sample REGION SHEET data

Running the macro on this data should create four workbooks named North.xlsx, South.xlsx, East.xlsx, and West.xlsx. Each workbook should contain the first three rows of boilerplate data, followed by the row of information for that region.

Note
The example shows only four items in the row for each region (region name, owner name, and their associated labels). Because the macro code is designed to copy the entire row, you can include additional information about the region in the row. Additionally, the code is designed to accommodate a variable number of region names, so the user can enter as many region names as necessary starting at cell B4.

The example shows only four items in the row for each region (region name, owner name, and their associated labels). Because the macro code is designed to copy the entire row, you can include additional information about the region in the row. Additionally, the code is designed to accommodate a variable number of region names, so the user can enter as many region names as necessary starting at cell B4.

The VBA code consists of three parts:

A macro to create a new worksheet for each region listed in column B.
A function in the macro that checks whether a worksheet with the same name exists before creating a new one.
A function in the macro that saves a region worksheet as a new workbook by using the region name as the file name.

Writing Code to Create New Worksheets

The basic strategy is to create a new worksheet for a region name, copy the data for that region to the new worksheet, and then save the new worksheet as a new workbook. When the new workbook is saved, the worksheet used to create that workbook is deleted. The following code example shows the entire CreateWorkbooks macro that creates new workbooks based on the contents of the REGION SHEET worksheet.

Sub CreateWorkbooks()
   Dim newSheet As Worksheet, regionSheet As Worksheet
   Dim cell As Object
   Dim regionRange As String

   Set regionSheet = Sheets("REGION SHEET")

   ' Turn off screen updating to increase performance.
   Application.ScreenUpdating = False

   ' Build a string that specifies the cells in column B that
   ' contain region names starting from cell B4.
   regionRange = "B4:" & regionSheet.Range("B4").End(xlDown).Address

   For Each cell In regionSheet.Range(regionRange)
      If SheetExists(cell.Value) = False Then
         ' Add a new worksheet.
         Sheets.Add After:=Sheets(Sheets.Count)
         ' Set newSheet variable to the new worksheet.
         Set newSheet = ActiveSheet
         ' Copy boilerplate data from first three rows 
         ' of the master worksheet to the range starting at
         ' A1 in the new sheet.
         regionSheet.Range("A1:A3").EntireRow.Copy newSheet.Range("A1")
         ' Copy and paste the column widths to the new sheet.
         regionSheet.Range("A1:A3").EntireRow.Copy
         newSheet.Range("A1").PasteSpecial xlPasteColumnWidths
         ' Copy the entire row for the current region and
         ' paste starting at cell A4 in the new sheet.
         cell.EntireRow.Copy newSheet.Range("A4")
         ' Name the new sheet.
         newSheet.Name = cell.Value
         ' Call the SaveWorkbook function to save the current
         ' worksheet as a new workbook file.
         SaveWorkbook (cell.Value)
         ' Turn off alerts, and then delete the new worksheet
         ' from the current workbook.
         Application.DisplayAlerts = False
         newSheet.Delete
         ' Turn alerts back on.
         Application.DisplayAlerts = True
      End If
   Next Cell

   ' Notify the user that the process is complete.
   MsgBox "All workbooks have been created successfully"

   ' Turn screen updating back on.
   Application.ScreenUpdating = True

End Sub

The first several lines declare variables that are used in the remaining lines of code to work with elements in the workbook. For example, the code uses the regionSheet variable to work with the REGION SHEET worksheet that contains the seed data for the new workbooks.

The following line of code creates a string that specifies the range of cells in column B that contain region names, and then assigns that value to the regionRange variable.

regionRange = "B4:" & regionSheet.Range("B4").End(xlDown).Address

The code that follows the & operator identifies the last cell in column B that contains a region name and returns its address. This enables you to add as many rows for region names as you need in column B, and then be sure that the code will create a workbook for every row that contains data. The code identifies the last cell that contains data by specifying the first cell that contains data, Range("B4"), and then calls the End property of the Range object with the xlDown enumeration to move the selection to the last cell in column B that contains data, returning a Range object that represents that cell. This is equivalent to selecting cell B4 in the worksheet and then pressing END+DOWN ARROW. For the sample data at the beginning of this section, this operation will move the selection to cell B7 (which contains the region name "WEST"). The code then uses the Address property of the Range object to return the cell's address.

The For Each...Next loop that follows then loops through each region name in the range specified by the regionRange variable to create a worksheet for each region. Before the macro attempts to create a new sheet, the If statement within the loop (If SheetExists(cell.Value) = False Then) passes the region's name to the SheetExists function to check whether a worksheet with the region's name already exists in the workbook. The code for the SheetExists function is another loop that checks each of the worksheets in the workbook's Sheets collection, and returns True if it finds an existing worksheet, or False, if it does not. The following example shows the code for the SheetExists function.

Function SheetExists(sheetName As String)
   Dim sheet As Worksheet
      For Each sheet In Sheets
         If sheet.Name = sheetName Then
            SheetExists = True
            Exit Function
         Else
            SheetExists = False
         End If
       Next
   End Function

The first line of code after the SheetExists check adds a new worksheet after the last sheet in the workbook's collection by using the Add method of the Sheets collection.

Sheets.Add After:=Sheets(Sheets.Count)

The remaining code in the CreateSheets macro calls the EntireRow property of the Range object to select the specified ranges of rows from the REGION SHEET worksheet, followed by a call to the Copy method to copy those rows to the new worksheet. Looking at the code, you might wonder why the Copy method is applied twice to the three rows of boilerplate information.

regionSheet.Range("A1:A3").EntireRow.Copy newSheet.Range("A1")

regionSheet.Range("A1:A3").EntireRow.Copy
newSheet.Range("A1").PasteSpecial xlPasteColumnWidths

This is done to preserve the column widths for wider cells in the boilerplate information. The first Copy method operation copies all of the data from the A1:A3 range in the REGION SHEET worksheet to the destination in the new worksheet, but using that operation alone does not preserve column width information. The second call to the Copy method does not pass a value for the Destination parameter, which causes the Copy method to copy the range data to the clipboard. The last line of code calls the PasteSpecial method with the xlPasteColumnWidths enumeration to paste the column widths to the destination in the new worksheet.

The next two lines of code name the new worksheet by using the name of the region, and then call the SaveWorkbook function to save the new worksheet as a new workbook file as shown in the following example.

' Name the new sheet.
newSheet.Name = cell.Value
' Call the SaveWorkbook function to save the current
' worksheet as a new workbook file.
SaveWorkbook (cell.Value)

The next section discusses how to write the code for the SaveWorkbook function.

The final lines of code inside the If statement delete the new worksheet after it has been saved as a new workbook by using the Delete method of the Worksheet object, as shown in the following example.

' Turn off alerts, and then delete the new worksheet
' from the current workbook.
Application.DisplayAlerts = False
newSheet.Delete
' Turn alerts back on.
Application.DisplayAlerts = True

Note that before calling the Delete method, the DisplayAlerts property of the Application object is set to False. That setting suppresses the confirmation dialog box that Excel displays when a user attempts to delete a worksheet that contains data.

The final lines of the CreateWorkbooks macro display a message box to notify the user that the process is complete by using the MsgBox function, and then turn screen updating back on as shown in the following example.

' Notify the user that the process is complete.
MsgBox "All workbooks have been created successfully"

' Turn screen updating back on.
Application.ScreenUpdating = True

Writing the Code to Create New Workbooks

The following example shows the code for the SaveWorkbook function.

Function SaveWorkbook(workbookName As String)
   Dim filePath As String

   filePath = "C:\Region Sheets\" & workbookName & ".xlsx"
   Sheets(workbookName).Copy
   ActiveWorkbook.SaveAs Filename:=filePath
   ActiveWorkbook.Close

End Function

Most of the code in this function is easy to understand. The code uses the filePath variable to specify the path and file name for the new workbook (using the name of the region as the file name). The code example assumes that you have a folder named C:\Region Sheets, but you can change the code to use any existing location.

The effect of the following line of code is less obvious.

Sheets(workbookName).Copy

This line of code uses the Copy method of the Sheets collection. When the Copy method is called without specifying either of the optional Before or After parameters, it copies the worksheet into a new workbook in memory rather than to another location in the collection of worksheets in the current workbook. The remaining two lines of code use the ActiveWorkbook property of the Application object to access the new workbook, and then call the SaveAs and Close methods of the Workbook object to save and close it.

Conclusion

In this article, you learn how to write two Visual Basic for Applications (VBA) macro solutions that automate operations on worksheets in Excel 2007. The first solution automates the process of naming and sorting worksheets in the current workbook. The second solution automates the process of creating and naming sets of worksheets based on data in the current workbook, and then saves each new worksheet as a new workbook file.

Additional Resources

For more information, see the following resources:

Share via