Creating VBA Macros to Manipulate Worksheets in Excel 2007
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
Applies to: Microsoft Office Excel 2007
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.
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:
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.