Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Export All Macros from One Workbook to Another Workbook?
By automating repetitive operations, macros are effective automation tools that can help you save time and effort. You might occasionally want to move a group of macros from one worksheet to another to make use of their features. In this article, we'll walk you through each step of exporting and importing all of the macros from one Excel file to another. By the time you're done, you'll be able to use the power of automation throughout your Excel projects because you'll have the knowledge and abilities necessary to move macros between workbooks with ease. Let's get going!
Export All Macros From One Workbook to Another Workbook
Here we will first create a VBA module and then run it to select the folder to complete the task. So let us see a simple process to know how you can export all macros from one workbook to another workbook in Excel.
Step 1
Consider any two Excel workbook one with multiple macros, other is a new sheet.
First, right-click on the sheet name and select View code to open the VBA application.
Right-click > View Code.
Step 2
Then click on Insert a Select Module, then copy the below code into the text box.
Code
Sub ExportImportMacros()
Dim SourceWorkbook As Workbook
Dim DestinationWorkbook As Workbook
Dim SourceModule As Object
Dim DestinationVBProject As Object
Set SourceWorkbook = Workbooks.Open("C:\Users
i31a\OneDrive\Desktop\old-workbook.xlsm")
Set DestinationWorkbook = Workbooks.Open("C:\Users
i31a\OneDrive\Desktop
ew-workbook.xlsm")
Set SourceModule = SourceWorkbook.VBProject.VBComponents.Item("Module1").CodeModule
Set DestinationVBProject = DestinationWorkbook.VBProject
On Error Resume Next
DestinationVBProject.VBComponents.Remove DestinationVBProject.VBComponents.Item("Module1")
On Error GoTo 0
DestinationVBProject.VBComponents.Add vbext_ct_StdModule
Dim DestinationModule As Object
Set DestinationModule = DestinationVBProject.VBComponents(DestinationVBProject.VBComponents.Count)
DestinationModule.CodeModule.AddFromString SourceModule.Lines(1, SourceModule.CountOfLines)
DestinationWorkbook.Save
SourceWorkbook.Close False
DestinationWorkbook.Close True
Set SourceModule = Nothing
Set DestinationModule = Nothing
Set DestinationVBProject = Nothing
Set SourceWorkbook = Nothing
Set DestinationWorkbook = Nothing
End Sub
In the code, old-workbook and new-workbook are the source and destination, respectively.
Step 3
Then click F5 to run the module. Then select the folder to export macros and click OK.
This is how you can export all macros from one sheet to another workbook in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can export all macros from one workbook to another workbook in Excel to highlight a particular set of data.
107 Views
