How the macro works

As you'll see when you read through the lines of the code, this macro is relatively intuitive:

Sub Macro1()

'Step 1 Copy the data
    Sheets("Example 1").Range("B4:C15").Copy

'Step 2 Create a new workbook
    Workbooks.Add

'Step 3 Paste the data
    ActiveSheet.Paste Destination:=Range("A1")

'Step 4 Turn off application alerts
    Application.DisplayAlerts = False

'Step 5 Save the newly created workbook
    ActiveWorkbook.SaveAs _
    Filename:="C:TempMyNewBook.xlsx"

'Step 6 Turn application alerts back on
    Application.DisplayAlerts = True

End Sub

In Step 1, you simply copy the data that ranges from cells B4 to C15. Note that you specify both the sheet and the range by name. This approach is a best practice when working with multiple open workbooks.

In Step 2, you use the Add method of the Workbook object to create a workbook. The blank workbook is equivalent to manually choosing File  ⇒  New  ⇒  Blank Document in the Excel ribbon.

In Step 3, you use the Paste method to send the copied data to cell A1 of the new workbook.

Pay attention to the fact that the code refers to the ActiveSheet object. When you add a workbook, the new workbook immediately gains focus, becoming the active workbook. (Excel does the same when you add a workbook manually.)

In Step 4 of the code, you set the DisplayAlerts method to False, effectively turning off Excel’s warnings. You do this because in the next step of the code, you save the newly created workbook. You may run this macro multiple times, in which case Excel attempts to save the file multiple times.

What happens when you try to save a workbook multiple times? That’s right — Excel warns you that there is already a file with that name and then asks if you want to overwrite the previously existing file. Because your goal is to automate the creation of the workbook, you want to suppress that warning.

In Step 5, you save the file by using the SaveAs method. Note that you enter the full path of the save location, including the final filename.

Because you turned off application alters in Step 4, you need to turn them back on (see Step 6). If you don’t, Excel continues to suppress all warnings during the current session.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.

4. Type or paste the code in the newly created module.

   You'll probably need to change the sheet name, the range address, and the save location.

How the macro works

The secret to this code is the Intersect method. Because you don’t want to save the worksheet when any old cell changes, you use the Intersect method to determine if the target cell (the cell that changed) intersects with the range specified as the trigger range (C5:C16 in this case).

The Intersect method returns one of two things: a Range object that defines the intersection between the two given ranges, or nothing. So in essence, you need to throw the target cell against the Intersect method to check for a value of Nothing. At that point, you can decide whether to save the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)

'Step 1: Does the changed range intersect?
    If Intersect(Target, Range("C5:C16")) Is Nothing Then

'Step 2: If there is no intersection, exit procedure
    Exit Sub

'Step 3: If there is an intersection, save the workbook
    Else
    ActiveWorkbook.Save

'Step 4: Close out the If statement
    End If

End Sub

In Step 1, you simply check to see whether the target cell (the cell that has changed) is in the range specified by the Intersect method. A value of Nothing means the target cell is outside the range specified.

Step 2 forces the macro to stop and exit the procedure if there is no intersection between the target cell and the specified range.

If there is an intersection, Step 3 fires the Save method of the active workbook, overwriting the previous version.

In Step 4, you simply close out the If statement. Every time you start an If-Then-Else check, you must close it out with a corresponding End If.

How to use the macro

To implement this macro, you need to copy and paste it into the Worksheet_Change event code window. Placing the macro here allows it to run each time you make any change to the sheet:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click the sheet from which you want to trigger the code.
4. In the Event drop-down list (see Figure 4-2), select the Change event.
5. Type or paste the code in the newly created module, changing the range address to suit your needs.

How the macro works

The code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The crux of the code is simple — it asks the users whether they want to close the workbook (see Figure 4-3). The macro then evaluates whether the user clicked OK or Cancel.

The evaluation is done with a Select Case statement. The Select Case statement is an alternative to the If-Then-Else statement, allowing you to perform condition checks in your macros. The basic construct of a Select Case statement is simple:

Select Case <some expression to check>
Case Is = <some value>
      <do something>
Case Is=<some other value>
      <do something else>
Case Is=<some 3rd value>
      <do some 3rd thing>
End Select

With a Select Case statement, you can perform many conditional checks. In this case, you are simply checking for OK or Cancel. Take a look at the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Activate the message box and start the check
    Select Case MsgBox("Save and close?", vbOKCancel)
'Step 2: Cancel button pressed, so cancel the close
    Case Is = vbCancel
    Cancel = True

'Step 3: OK button pressed, so save the workbook and close
    Case Is = vbOK
    ActiveWorkbook.Save

'Step 4: Close your Select Case statement
End Select

End Sub

In Step 1, you activate the message box as the condition check for the Select Case statement. You use vbOKCancel argument to ensure that the OK and Cancel buttons are presented as choices.

In Step 2, if the user clicked Cancel in the message box, the macro tells Excel to cancel the Workbook_Close event by passing True to the Cancel Boolean.

If the user clicked the OK button in the message box, Step 3 takes effect. Here, you tell Excel to save the workbook. And because you didn't set the Cancel Boolean to True, Excel continues with the close.

In Step 4, you simply close out the Select Case statement. Every time you instantiate a Select Case, you must close it out with a corresponding End Select.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro there allows it to run each time you try to close the workbook:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click ThisWorkbook.
4. In the Event drop-down list (see Figure 4-4), select the BeforeClose event.
5. Type or paste the code in the newly created module.

How the macro works

The code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The macro automatically protects the specified sheet with the given password, and then saves the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Protect the sheet with a password
      Sheets("Sheet1").Protect Password:="RED"

'Step 2: Save the workbook
      ActiveWorkbook.Save

End Sub

In Step 1, you're explicitly specifying which sheet to protect — Sheet1, in this case. You also provide the password argument, Password:="RED", which defines the password needed to remove protection.

This password argument is optional. If you omit it, the sheet will still be protected, but you won’t need a password to unprotect it.

Excel passwords are case-sensitive, so you’ll want to pay attention to the exact password and capitalization that you are using.

Step 2 tells Excel to save the workbook. If you don’t save the workbook, the sheet protection you just applied won’t be in effect the next time the workbook is opened.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click ThisWorkbook.
4. In the Event drop-down list (see Figure 4-5), select the BeforeClose event.
5. Type or paste the code in the newly created module, modifying the sheet name (if necessary) and the password.

Note that you can protect additional sheets by adding addition statements before the Activeworkbook.Save statement.

How the macro works

The code is triggered by the workbook’s Open event. When you open a workbook, this event triggers, running the code within. This macro automatically unprotects the specified sheet with the given password when the workbook is opened:

Private Sub Workbook_Open()

'Step 1: Protect the sheet with a password
     Sheets("Sheet1").Unprotect Password:="RED"

End Sub

The macro explicitly names the sheet you want to unprotect — Sheet1, in this case. Then it passes the password required to unprotect the sheet.

Excel passwords are case-sensitive, so pay attention to the exact password and capitalization that you are using.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_Open event code window. Placing the macro here allows it to run each time the workbook is opened:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click ThisWorkbook.
4. In the Event drop-down list (see Figure 4-6), select the Open event.
5. Type or paste the code in the newly created module, modifying the sheet name (if necessary) and the password.

How the macro works

This macro uses the workbook’s Open event to start the workbook on the specified sheet when the workbook is opened:

Private Sub Workbook_Open()

'Step 1: Select the specified sheet
     Sheets("Start Here").Select

End Sub

The macro explicitly names the sheet the workbook should jump to when it's opened.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_Open event code window. Placing the macro here allows it to run each time the workbook is opened:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click ThisWorkbook.
4. In the Event drop-down list (see Figure 4-8), select the Open event.
5. Type or paste the code in the newly created module, changing the sheet name, if necessary.

How the macro works

This macro opens the dialog box you see in Figure 4-9, allowing the user to browse for and open an Excel file.

Here's how this macro works:

Sub Macro1()

'Step 1: Define a String variable
    Dim FName As Variant
'Step 2: GetOpenFilename Method activates dialog box
    FName = Application.GetOpenFilename( _
            FileFilter:="Excel Workbooks,*.xl*", _
            Title:="Choose a Workbook to Open", _
            MultiSelect:=False)

'Step 3: If a file was chosen, open it!
    If FName <> False Then
    Workbooks.Open Filename:=FName
    End If

End Sub

In Step 1, the macro declares a Variant variable that holds the filename that the user chooses. FName is the name of your variable.

In Step 2, you use the GetOpenFilename method to call up a dialog box that allows you to browse and select the file you need.

The GetOpenFilename method supports a few customizable parameters. The FileFilter parameter allows you to specify the type of file you're looking for. The Title parameter allows you to change the title that appears at the top of the dialog box. The MultiSelect parameter allows you to limit the selection to one file.

If the user selects a file from the dialog box, the FName variable is filled with the chosen filename. In Step 3, you check for an empty FName variable. If the variable is not empty, you use the Open method of the Workbooks object to open the file.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11 on your keyboard.
2. Right-click project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

5. (Optional) Assign the macro to a button.

   For details, see the section on assigning a macro to a button in Chapter 1.

How the macro works

The first thing to notice about this macro is that it is a function, not a Sub procedure. As you will see, making this macro a function enables you to pass any filename to it to test whether that file is already open.

The gist of this code is simple. You're testing a given filename to see if it can be assigned to an Object variable. Only opened workbooks can be assigned to an Object variable. When you try to assign a closed workbook to the variable, an error occurs.

If the given workbook can be assigned, the workbook is open; if an error occurs, the workbook is closed.

Function FileIsOpenTest(TargetWorkbook As String) As Boolean

'Step 1: Declare your variables
    Dim TestBook As Workbook

'Step 2: Tell Excel to resume on error
    On Error Resume Next

'Step 3: Try to assign the target workbook to TestBook
    Set TestBook = Workbooks(TargetWorkbook)

'Step 4: If no error occurred, workbook is already open
    If Err.Number = 0 Then
    FileIsOpenTest = True
    Else
    FileIsOpenTest = False
    End If

End Function

The first thing the macro does is to declare a String variable that will hold the filename that the user chooses. TestBook is the name of your String variable.

In Step 2, you tell Excel that there may be an error running this code an, in the event of an error, resume the code. Without this line, the code would simply stop when an error occurs. Again, you test a given filename to see if it can be assigned to an Object variable. If the given workbook can be assigned, it’s open; if an error occurs, it’s closed.

In Step 3, you attempt to assign the given workbook to the TestBook Object variable. The workbook you try to assign is a String variable called TargetWorkbook. TargetWorkbook is passed to the function in the function declarations (see the first line of the code). This structure eliminates the need to hard-code a workbook name, allowing you to pass it as a variable instead.

In Step 4, you simply check to see if an error occurred. If an error did not occur, the workbook is open, so you set the FileIsOpenTest to True. If an error occurred, the workbook is not open and you set the FileIsOpenTest to False.

Again, this function can be used to evaluate any file you pass to it, via its TargetWorkbook argument. This is the beauty of putting the macro into a function.

The following macro demonstrates how to implement this function. Here, you use the same macro you saw in the previous section, “Opening a Specific Workbook Defined by the User,” but this time, you call the new FileIsOpenTest function to make sure that the user cannot open an already opened file:

Sub Macro1()

'Step 1: Define a String variable
    Dim FName As Variant
    Dim FNFileOnly As String

'Step 2: GetOpenFilename Method activates dialog box
    FName = Application.GetOpenFilename( _
            FileFilter:="Excel Workbooks,*.xl*", _
            Title:="Choose a Workbook to Open", _
            MultiSelect:=False)

'Step 3: Open the chosen file if not already opened
    If FName <> False Then
    FNFileOnly = StrReverse(Left(StrReverse(FName), _
InStr(StrReverse(FName), "") - 1))

        If FileIsOpenTest(FNFileOnly) = True Then
            MsgBox "The given file is already open"
        Else
            Workbooks.Open Filename:=FName
        End If
    End If

End Sub

With this macro implemented, you get the friendlier message box shown in Figure 4-11.

How to use the macro

To implement this macro, you can copy and paste both pieces of code into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

5. (Optional) Assign the macro to a button.

   For details, see the section on assigning a macro to a button in Chapter 1.

How the macro works

The first thing to notice about this macro is that it is a function, not a Sub procedure. Making this macro a function enables you to pass any file path to it.

In this macro, you use the Dir function, which returns a string that represents the name of the file that matches what you pass to it. This function can be used in lots of ways, but here, you use it to check whether the file path you pass to it exists:

Function FileExists(FPath As String) As Boolean

'Step 1: Declare your variables
    Dim FName As String

'Step 2: Use the Dir function to get the filename
    FName = Dir(FPath)

'Step 3: If file exists, return True; else False
    If FName <> "" Then FileExists = True _
    Else: FileExists = False

End Function

Step 1 declares a String variable that holds the filename that returns from the Dir function. FName is the name of the String variable.

In Step 2, you attempt to set the FName variable. You do this by passing the FPath variable to the Dir function. This FPath variable is passed via the function declarations (see the first line of the code). This structure prevents you from having to hard-code a file path, passing it as a variable instead.

If the FName variable can’t be set, the path you passed does not exist. Thus the FName variable is empty. Step 3 merely translates that result to a True or False expression.

Again, this function can be used to evaluate any file path you pass to it. This is the beauty of writing the macro as a function.

The following macro demonstrates how to use this function:

Sub Macro1()

    If FileExists("C:TempMyNewBook.xlsx") = True Then
         MsgBox "File exists."
    Else
         MsgBox "File does not exist."
    End If

End Sub

How to use the macro

To implement this macro, you can copy and paste both pieces of code into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

How the macro works

In this macro, the Workbooks collection loops through all opened workbooks. As the macro loops through each workbook, it saves and closes them down:

Sub Macro1()

'Step 1: Declare your variables
    Dim wb As Workbook
'Step 2: Loop through workbooks, save and close
    For Each wb In Workbooks
        wb.Close SaveChanges:=True
    Next wb

End Sub

Step 1 declares an Object variable that represents a Workbook object. This allows you to enumerate through all the open workbooks, capturing their names as you go.

Step 2 simply loops through the open workbooks, saving and closing them. If you don’t want to save them, change the SaveChanges argument from True to False.

How to use the macro

The best place to store this macro is in your personal macro workbook. This way, the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, it doesn't exist yet. You’ll have record a macro using personal macro workbook as the destination.

To record the macro in your personal macro workbook, open the Record Macro dialog box. In the Store Macro In drop-down list, select Personal Macro Workbook. Then simply record a few cell clicks and stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

In this macro, you use the Dir function to return a string that represents the name of the file that matches what you pass to it.

You use the Dir function to enumerate through all .xlsx files in a given directory, capturing each file’s name. Then you open each file, print it, and then close it.

Sub Macro1()

'Step 1:Declare your variables
    Dim MyFiles As String

'Step 2: Specify a target directory
    MyFiles = Dir("C:Temp*.xlsx")
    Do While MyFiles <> ""

'Step 3: Open workbooks one by one
    Workbooks.Open "C:Temp" & MyFiles
    ActiveWorkbook.Sheets("Sheet1").PrintOut Copies:=1
    ActiveWorkbook.Close SaveChanges:=False

'Step 4: Next file in the directory
    MyFiles = Dir
    Loop

End Sub

Step 1 declares the MyFiles String variable that will capture each filename in the enumeration.

Step 2 uses the Dir function to specify the directory and file type you are looking for. Note that the code is looking for *.xlsx, so only xlsx files will be looped through. If you want to look for .xls files, you will need to specify that (along with the directory you need to search). The macro passes any filename it finds to the MyFiles String variable.

Step 3 opens the file and then prints one copy of Sheet1. Needless to say, you will probably want to change which sheets to print. You can also change the number of copies to print.

Step 4 loops back to find more files. If there are no more files, the MyFiles variable is blank and the loop and the macro end.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module, modifying the print statement as needed.

How the macro works

This code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. This macro checks to see if the target cell (cell C7, in this case) is empty. If it is empty, the close process is cancelled. If C7 is not empty, the workbook is saved and closed:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Check to see if cell C7 is blank
If Sheets("Sheet1").Range("C7").Value = "" Then

'Step 2: If cell is blank, cancel the close and tell user
    Cancel = True
    MsgBox "Cell C7 cannot be blank"

'Step 3: If cell is not blank, save and close
Else
    ActiveWorkbook.Close SaveChanges:=True
End If

End Sub

Step 1 checks to see whether C7 is blank.

If C7 is blank, Step 2 takes effect, cancelling the close process by passing True to the Cancel Boolean. Step 2 also activates a message box notifying the user of his or her stupidity (well, it's not quite that harsh, really).

In Step 3, if cell C7 is not blank, the workbook is saved and closed.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click ThisWorkbook.
4. In the Event drop-down list (see Figure 4-13), select the BeforeClose event.
5. Type or paste the code in the newly created module.

How the macro works

The trick to this macro is piecing together the new filename as the path, today’s date, and the original filename.

The path is captured by using the Path property of the ThisWorkbook object. Today's date is grabbed with the Date function.

By default, the Date function returns mm/dd/yyyy. Forward slashes would cause the file save to fail, so you format the date using hyphens instead (Format(Date, "mm-dd-yy")) because Windows does not allow forward slashes in filenames.

The last piece of the new filename is the original filename. You capture it by using the Name property of the ThisWorkbook object:

Sub Macro1()

'Step 1: Save workbook with new filename
     ThisWorkbook.SaveCopyAs _
     Filename:=ThisWorkbook.Path & "" & _
     Format(Date, "mm-dd-yy") & " " & _
     ThisWorkbook.Name

End Sub

In the one and only step, the macro builds a new filename and uses the SaveCopyAs method to save the file.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.