UPGRADE

How to Save Time and Effort by Copying VBA Macros from One Excel Workbook to Another

Last updated on April 20, 2023 By Victor Chan

Excel can be a powerful tool, but it can also be tedious and complex. VBA (Visual Basic for Applications) macros can make your life easier by automating tasks and simplifying calculations.


But creating macros from scratch in every new workbook can be a hassle. In this article, we'll teach you how to copy or import VBA macros from one workbook to another, saving you time and energy.


That's not all: we'll also give you some useful bonus VBA code that you can copy into your own Personal Macro Workbook for easy access.


And you'll meet Code Maverick, master of VBA and seeker of justice. He'll inspire you with his agility with code and his quest for efficiency. Are you ready to code like a hero and have fun with Excel?


What are VBA macros and how do they work in Excel?

The basics of VBA macros

VBA macros are small programs written in Visual Basic for Applications, a programming language designed by Microsoft. They allow you to automate tasks in Excel by executing a series of commands. You can create VBA macros in the Visual Basic Editor (VBE), which is built into Excel.

The benefits of using VBA macros in Excel

VBA macros can:

  • Automate repetitive tasks
  • Perform complex calculations efficiently
  • Customize Excel functionality
  • Integrate Excel with other Microsoft Office applications
  • Enhance your overall productivity

For further VBA learning, check out how to record and run macros and edit macros. And I have a list of some common and useful VBA commands here.

How to copy or import VBA macros from one workbook to another in Excel

Here’s Code Maverick, a veteran VBA ace. Together, we'll explore four different methods to help you transfer your Excel VBA macros from one workbook to another.


These options range from the straightforward manual copy-pasting of code to more advanced techniques like exporting and importing modules.


Whether you're new to VBA or an experienced user, we've got you covered, so you can find the method that best suits your needs and skill level. Or learn a new one!


Let's dive in and discover the various ways you can efficiently copy or import VBA macros between Excel workbooks.

Method 1: Copy and paste the VBA code manually

This is the straight-shooting, no-nonsense, way to copy code. It’s what most people start with. And it works. Until it doesn’t. You see, it’s easy to slip up and copy/paste the wrong lines. Even one mistake and you’ve messed up the code.


Let’s hear from Code Maverick…

I got myself tangled up in copying VBA code. Around midnight. I was sippin' on cold coffee and fiddling with the Visual Basic Editor. A late-night task, not a daytime chore. I was weary, and my eyes were strained from hours of squinting at the screen. All the way from the first line of code to the very last.


The manual method, that's what I chose. A simple job—copy and paste. A no-nonsense approach. But with it came risks, and I knew it. The chances of slipping up, missing a beat, or dropping code in the wrong place. Yeah, I was playing with fire.


External references, they don't follow you, not like a shadow. You gotta bring 'em along for the ride, all by yourself. And the code's format? Well, that's a gamble, too. The structure, the comments, they might not stick. A messier job than I'd like to admit.


But I rolled with it, embracing the uncertainty. The immediacy of it all, the raw control over what VBA code to keep and what to let go. It was a dance with chance, and I was leading. Manual copy-pasting, a straightforward but risky game, and I was all in.

So use this method for quick copy/paste action. But beware of its limitations!


Here are the steps laid out if you need them (basically copy and paste):

  1. 1. Open both workbooks (source and destination) in Excel.
  2. 2. Press Alt + F11 to open the Visual Basic Editor (VBE).
  3. 3. In the VBE, find the source workbook in the Project Explorer window (usually on the left side).
  4. 4. Expand the source workbook's Modules folder and double-click on the module containing the macro you want to copy.
  1. 5. Select and copy the code (Ctrl + C).
  2. 6. In the Project Explorer, find the destination workbook and right-click on its Modules folder.
  3. 7. Choose Insert > Module to create a new module in the destination workbook.
  4. 8. Double-click on the new module and paste the code (Ctrl + V).
  5. 9. Close the VBE by pressing Alt + Q.

PRO TIP: You can arrange the module windows side-by-side for even easier copy-and-paste action! In the VB Editor, click on Window, then Tile Vertically. Close any windows you don’t need, and rearrange as required.

Method 2: Export and import the VBA modules

OK, we now move onto a more sophisticated method. Here are three reasons why Code Maverick recommends you use this over simple copying and pasting of code.

  1. 1. Portability and sharing: Exporting a VBA module as a .bas file creates a standalone file that can be easily shared with others or stored for future use. This method is particularly useful when you want to distribute your VBA code to multiple users or save a backup copy of the module for version control.
  2. 2. Clean code transfer: Importing a VBA module ensures that the entire code, including its structure, formatting, and comments, is accurately transferred to the destination workbook. This method helps maintain the original code organization and reduces the likelihood of errors introduced during manual copy-pasting.
  3. 3. Batch import/export: If you have multiple VBA modules to transfer, the export and import method allows you to manage them more efficiently. You can batch export all the modules as separate files and then import them into the destination workbook, streamlining the process and ensuring that all the code is transferred correctly.

"Export and import, the smooth operator of VBA transfers; clean, efficient, and always on target." - Code Maverick

Here are the steps for doing this method:

  1. 1. Open both workbooks in Excel.
  2. 2. Press Alt + F11 to open the Visual Basic Editor (VBE).
  3. 3. In the Project Explorer, find the source workbook and right-click on the module you want to export.
  4. 4. Choose Export File and save the module as a .bas file on your computer.
  1. 5. In the Project Explorer, find the destination workbook and right-click on its Modules folder.
  2. 6. Choose Import File and select the .bas file you previously saved.
  3. 7. The module will be imported into the destination workbook.
  4. 8. Close the VBE by pressing Alt + Q.

Method 3: Drag and drop a VBA code module

This method involves dragging a VBA code module from one workbook's Visual Basic Editor (VBE) to another workbook's VBE. According to Code Maverick, here are the pros and cons of using this method:

Drag-n-drop Pros:

  1. Simplicity: Dragging and dropping a VBA code module is a quick and straightforward way to transfer VBA code between workbooks without manually copying and pasting the code.
  2. Preserves code structure: This method ensures that the entire code module, including its formatting, comments, and structure, is transferred accurately to the destination workbook.
  3. Immediate code transfer: The drag-and-drop method allows you to instantly move the VBA code module between workbooks, without the need to save or export a separate file.
  4. Module-level transfer: The drag-and-drop method is ideal when you want to transfer an entire module that contains multiple procedures or functions, as it moves the entire module in one step.

Drag-n-drop Cons:

  1. Limited to VBE: To use this method, you need to have both the source and destination workbooks open in the Visual Basic Editor (VBE). This requirement may be inconvenient if you are not used to working within the VBE.
  2. No automatic reference updates: If the VBA code relies on external references, such as libraries or Add-Ins, you need to manually add these references to the destination workbook. The drag-and-drop method does not automatically update or transfer these dependencies.
  3. Version compatibility: The drag-and-drop method does not resolve any compatibility issues between different versions of Excel. If the code uses features or functions specific to one version of Excel, you may need to adjust the code for compatibility in the destination workbook.
  4. Potential for errors: Since this method involves manual dragging and dropping, there is a risk of accidentally moving the code to the wrong location or workbook, potentially leading to errors or confusion.

"Drag and drop, a quick dance between workbooks, where the VBA code slides in with a swift move." - Code Maverick

In summary, the drag-and-drop method is simple and immediate. But it requires working within the VBE and may involve additional manual steps to ensure proper functionality in the destination workbook.


Here’s how you do it:

  1. 1. Open both workbooks in Excel.
  2. 2. Press Alt + F11 to open the Visual Basic Editor (VBE).
  3. 3. Arrange the VBE windows so you can see both the source and destination workbook in the Project Explorer.
  1. 4. Click and hold the module you want to copy from the source workbook.
  2. 5. Drag and drop the module onto the destination workbook in the Project Explorer.
  3. 6. The module will be copied to the destination workbook.
  4. 7. Close the VBE by pressing Alt + Q.

Method 4: Copy sheet modules that have code

Did you know that individual Excel sheets can store code? It’s a fact! According to Code Maverick, here are several reasons why you’d want to use method 4 for transferring VBA macros in sheet modules between workbooks:

  1. 1. Preserve sheet-specific code: Sometimes, VBA macros are written in the sheet module (also known as the worksheet code module) instead of the standard code module. These macros are often designed to work specifically with a particular sheet's data or events (e.g., when a cell's value changes or the sheet is activated). By copying the entire sheet, you ensure that both the sheet's data and the associated VBA code are transferred together, maintaining their functionality in the destination workbook.
  2. 2. Minimize manual steps: When copying sheet modules that have code, you don't need to open the Visual Basic Editor or manipulate the code directly. Instead, you can use Excel's built-in "Move or Copy" feature to transfer the sheet and its VBA code in just a few clicks. This method saves time and reduces the chance of errors when moving the VBA code between workbooks.
  3. 3. Maintain code and data relationships: If your VBA code interacts with specific cells, ranges, or named ranges within the sheet, copying the entire sheet ensures that these relationships are preserved in the destination workbook. This approach eliminates the need to recreate or adjust cell references in the VBA code after transferring it.
  4. 4. Simplify sharing with others: If you want to share a specific sheet containing VBA code with colleagues or collaborators, copying the sheet module that has code is an efficient way to share both the sheet's data and the VBA functionality. This method ensures that the recipient can immediately use the sheet and its associated macros without having to import or set up the VBA code separately.

"Copy sheet modules, the all-in-one package deal—data and code, hand in hand, moving to the rhythm of efficiency.” - Code Maverick

With that in mind, here are the steps:

  1. Open both workbooks in Excel.
  2. In the source workbook, right-click on the sheet tab containing the VBA code you want to copy.
  3. Choose Move or Copy.
  1. 4. In the To book dropdown, select the destination workbook.
  2. 5. Check the Create a copy box and click OK.
  3. 6. The sheet with the VBA code will be copied to the destination workbook.

Copy VBA macros to your personal macro workbook

Why the personal macro workbook is useful

The Personal Macro Workbook (PMW) is a hidden workbook that opens automatically when you start Excel. By storing macros in the PMW, you can access them in any workbook without having to copy or import them.


Don’t have one yet? Learn more about how to set up your own Personal Macro Workbook.


“Personal Macro Workbooks are the secret arsenal of productivity—custom-made tools, always at your fingertips, ready for action.”

- Code Maverick

Top VBA macros to copy to your personal macro workbook

Here is a list of top macros that you can consider keeping in your Excel personal macro workbook (PMW):


1. Unhide all sheets: This macro unhides all hidden sheets in the workbook.

Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

2. Hide all but the active sheet: This macro hides all sheets except the active one.

Sub HideAllButActiveSheet()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
End Sub

3. Delete all empty rows: This macro deletes all empty rows in the active sheet.

Sub DeleteEmptyRows()
    On Error Resume Next
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

4. Clear all filters: This macro removes filters from the active sheet.

Sub ClearAllFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

5. Create a table of contents: This macro generates a table of contents with hyperlinks to all sheets in the workbook.

Sub CreateTableOfContents()
    Dim ws As Worksheet
    Dim wsTOC As Worksheet
    Dim i As Long

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Table of Contents").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsTOC = Sheets.Add(Before:=Sheets(1))
    wsTOC.Name = "Table of Contents"

    i = 1
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Table of Contents" Then
            wsTOC.Hyperlinks.Add Anchor:=wsTOC.Cells(i, 1), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
            i = i + 1
        End If
    Next ws
End Sub

6. Toggle gridlines: This macro toggles gridlines on and off in the active sheet.

Sub ToggleGridlines()
    ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
End Sub

7. Save each worksheet as a separate PDF: This macro saves each worksheet in the active workbook as a separate PDF file.

Sub SaveWorksheetsAsPDF()
    Dim ws As Worksheet
    Dim pdfPath As String

    pdfPath = "C:\PDFs\" ' Set your desired folder path here

    For Each ws In ActiveWorkbook.Worksheets
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
                               Filename:=pdfPath & ws.Name & ".pdf", _
                               Quality:=xlQualityStandard, _
                               IncludeDocProperties:=True, _
                               IgnorePrintAreas:=False, _
                               OpenAfterPublish:=False
    Next ws
End Sub

8. AutoFit all columns: This macro autofits all columns in the active sheet.

Sub AutoFitAllColumns()
    ActiveSheet.Cells.EntireColumn.AutoFit
End Sub

9. Protect or unprotect all worksheets: This macro toggles protection on and off for all worksheets in the active workbook. Replace "YourPassword" with your desired password.

Sub ToggleSheetProtection()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.ProtectContents = False Then
            ws.Protect Password:="YourPassword"
        Else
            ws.Unprotect Password:="YourPassword"
        End If
    Next ws
End Sub

10. Insert a new worksheet and rename it: This macro inserts a new worksheet and prompts you for a name.

Sub InsertAndRenameWorksheet()
    Dim ws As Worksheet
    Dim wsName As String
    
    wsName = InputBox("Enter the new sheet name:", "New Sheet Name")
    
    If wsName <> "" Then
        Set ws = ActiveWorkbook.Worksheets.Add
        On Error Resume Next
        ws.Name = wsName
        If Err.Number <> 0 Then
            MsgBox "Invalid sheet name or the name already exists. Please try again."
            ws.Delete
        End If
        On Error GoTo 0
    End If
End Sub

These macros can be helpful for various tasks in Excel. I like to keep my favorite macros in my own PMW for easy reference. Either to perform tasks in a workbook, or to help me with code that I use often.

Remember to save them in your personal macro workbook (PERSONAL.XLSB) so that they are easily accessible whenever you need them.

And if you don’t have one, learn more about how to set up your own Personal Macro Workbook.

Wrapping It Up: Key Takeaways and Your Next Steps

You've learned a lot in this article. You've discovered how to copy or import VBA macros from one workbook to another in Excel, making your life easier.


You've also learned how to use the Personal Macro Workbook to store your most-loved macros. This knowledge will help you save time and energy, and let you focus on the things that matter. Awesome, right?


Don't stop here. Keep practicing these techniques and store your frequently-used macros in the Personal Macro Workbook.


As you master VBA, you'll see how powerful it is to automate tasks and customize Excel's features. You'll become more efficient and have more fun with Excel. Just like Code Maverick, the master of VBA and the seeker of justice. Be inspired by his example and code like a hero!


FAQs: Copying and Importing Excel VBA Macros

1. What's the difference between copying and importing VBA macros in Excel?

Copying a VBA macro involves manually selecting and transferring the code from one workbook to another. On the other hand, importing a VBA macro refers to the process of exporting a macro as a .bas file from the source workbook and then importing that file into the destination workbook.

2. Can I copy multiple VBA macros at once?

Yes, you can copy multiple VBA macros at once by selecting all the relevant code sections or modules in the source workbook and then pasting them into the destination workbook. This can be done using methods such as copy-pasting code, exporting and importing modules, or dragging and dropping modules.

3. Is it possible to import VBA macros from other file formats into Excel?

Yes, VBA macros can be imported from other file formats, such as .txt and .bas, into Excel. To do this, you can use the "Import File" option available in the Visual Basic Editor (VBE) and select the desired file containing the VBA code.

4. How do I ensure that the copied VBA macros work correctly in the new workbook?

After copying or importing VBA macros into a new workbook, it's essential to check whether the macros work correctly. You can do this by:

  • Verifying that all necessary references are included in the new workbook (e.g., external libraries or Add-Ins)
  • Ensuring that the macro is assigned to the appropriate objects, such as buttons or shapes
  • Testing the macro in the new workbook to confirm its functionality

5. Can I share workbooks containing VBA macros with others who don't have the macros installed?

Yes, you can share workbooks containing VBA macros with others, and the macros will remain functional in the shared workbook. However, the recipient must enable macros in their Excel settings to use the VBA macros. It's also essential to ensure that the macros are compatible with the recipient's Excel version and operating system.

Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!

Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Learn VBA

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get help now!

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Get it here

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

Learn More

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Find out...

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Read on

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

Show me!

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.