by David Hakala
Sometimes you have a repetitive task that cannot be recorded by Excel’s macro recorder. Take, for instance, this fairly common example. You need to create a new worksheet, give it a name, place it at the end of all the tabs in your workbook, and save the updated workbook.
If you try to use the macro recorder, you will end up with VBA code that looks something like this:
Sub Macro1()
Sheets(“Master”).Select
Sheets(“Master”).Copy After:=Sheets(3)
Sheets(“Master (2)”).Select
Sheets(“Master (2)”).Name = “NewMaster”
End Sub
There are two problems with this code. First, it will only save a new worksheet in the third position in the worksheet tabs list. Second, it always names the new worksheet “NewMaster,” so an error message will pop up the second time you run the macro. What we want is to always save the new worksheet at the end of the tabs list, no matter what absolute position that may be, and we want the opportunity to give the new worksheet a unique name of our choice.
You can tweak the recorded code to produce the desired results, something like this:
Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
Worksheets(“Master”).Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:=”Enter new worksheet name”)
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
End Sub
This code will copy the worksheet named “Master” to the end of the tabs list and keep prompting you for a new worksheet name until a valid name is entered. This feature covers the instance in which you enter a name that already exists.
To save the code above, open Excel and the workbook you want to add the macro to. Press Alt-F11 to open the VBA editor. Select the “ThisWorkbook” object. Copy the code and paste it into the editing window. Then save the macro and give it a name.
Worksheet macro
David Hakala has perpetrated technology tutorials since 1988 in addition to committing tech journalism, documentation, Web sites, marketing collateral, and profitable prose in general. His complete rap sheet can be seen at http://www.linkedin.com/in/dhakala