Author: Oscar Cronquist Article last updated on October 11, 2019 Show
 This article demonstrates several VBA macros, they will save you time if you have lots of worksheets. The first macro copies worksheets from the active workbook to new workbooks, each worksheet becomes a new workbook. The second VBA macro copies selected worksheets to new workbooks, each worksheet becomes a new workbook. The third VBA macro copies worksheets from all open workbooks and collects them all to a single master workbook. The last macro names the created worksheets based on the original workbook name. Table of Contents
Copy each sheet in active workbook to new workbooksThe following macro copies each sheet in the current workbook to new workbooks. VBA code'Name macro Sub CopySheetsToNewWorkbooks() 'Dimension variable and declare data types Dim SHT As Worksheet 'Iterate through worksheets in active workbook For Each SHT In ActiveWorkbook.Worksheets 'Copy worksheet to a new workbook SHT.Copy 'Continue with next worksheet in acteive workbook Next 'Stop macro End Sub Copy selected sheets to new workbooksIf a sheet contains a list (excel 2003) or a table (excel 2007) you can't only use the SelectedSheets property to copy selected sheets. This article by Ron de Bruin explains a workaround: Copying Worksheets with a List or Table
VBA code'Name macro Sub CopySelectedSheetsToNewWorkbooks() 'Dimension variable and declare data types Dim AW As Window 'The SET statement saves the active window as an object reference to variable AW Set AW = ActiveWindow 'Iterate through selected worksheets based on active window For Each SHT In AW.SelectedSheets 'The NewWindow method creates a new window based on the active window using an object reference saved to variable TempWindow Set TempWindow = AW.NewWindow 'Copy worksheet to a new workbook SHT.Copy 'Close the newly created window TempWindow.Close 'Continue with next worksheet Next 'Stop macro End Sub Copy sheets in every open workbook to a master workbookThis macro copies all sheets in all open workbooks to a singlemaster workbook. 'Name macro
Sub CopySheetsToMasterWorkbook()
'Dimension variables and declare data types
Dim WBN As Workbook, WB As Workbook
Dim SHT As Worksheet
'Create a new workbook and save an object reference to variable WBN
Set WBN = Workbooks.Add
'Iterate through all open workbooks
For Each WB In Application.Workbooks
'Check if workbook name of object variable WB is not equal to name of object variable WBN
If WB.Name <> WBN.Name Then
'Go through all worksheets in object WB
For Each SHT In WB.Worksheets
'Copy worksheet to workbook WBN and place after the last worksheet
SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count)
'Continue with next worksheet
Next SHT
'End of If statement
End If
'Continue with next workbook
Next WB
'Disable Alerts
Application.DisplayAlerts = False
'Delete sheet1, sheet2 and sheet3 in the new workbook WBN
WBN.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
'Enable Alerts
WBN.Application.DisplayAlerts = True
'End macro
End Sub
Add workbook name to sheetsPerhaps you want to know where each sheet in the master workbook came from? The following line adds the original workbook name to the sheet name. Copy and paste this line WBN.Sheets(WBN.Worksheets.Count).Name = Left(WB.Name, 30 - Len(SHT.Name)) & "-" & SHT.Name after this line SHT.Copy After:=WBN.Sheets(WBN.Worksheets.Count) in the macro above. Where to put the macros?
Note, make sure you save the workbook with file extension *.xlsm so the code stays with your workbook. Recommended reading10 ways to reference Excel workbooks and sheets using VBA
Apply drop-down lists dynamically
Auto resize columns as you type
Automate data entry [VBA]
Basic data entry [VBA]
Calendar with scheduling [vba]
Consolidate sheets [vba]
Copy a dynamic cell range [VBA]
Create a Print button [VBA]
Edit invoice data [VBA]
Excel calendar [VBA]
Hide specific columns programmatically
Hide specific worksheets programmatically
How to quickly select blank cells
How to use DIALOG BOXES
How to use the Scroll Bar
Identify missing numbers in a column
Locate a shape in a workbook
Move a shape [VBA]
Normalize data [VBA]
Open Excel files in a folder [VBA]
Save invoice data [VBA]
Search two related tables [VBA]
Select and view invoice [VBA]
Toggle a macro on/off using a button
Working with COMBO BOXES [Form Controls]
Working with FILES
Working with TEXT BOXES [Form Controls] Latest updated articles. More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas. More than 1300 formulas organized in subcategories. Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more. Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location. Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid. Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell. Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references. The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations. An Excel feature that lets you visualize data in a graph. Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula. Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently. VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions. A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros. UDF stands for User Defined Functions and is custom built functions anyone can create. A list of all published articles. Can you copy data from one worksheet to another using a VBA program?We can copy a value and paste it to another cell. We can use Paste Special to paste only the values. Similarly, in VBA, we use the copy method with range property to copy a value from one cell to another. We use the worksheet function Paste Special or the paste method to paste the value.
How do I copy multiple worksheets to a new workbook?Here's how:. Select all the data in the worksheet. Keyboard shortcut: Press CTRL+Spacebar, on the keyboard, and then press Shift+Spacebar.. Copy all the data on the sheet by pressing CTRL+C.. Click the plus sign to add a new blank worksheet.. Click the first cell in the new sheet and press CTRL+V to paste the data.. How do I export and save each worksheet as a new workbook in Excel VBA?Step 1: Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. Step 2: Click Insert > Module, and paste the following macro in the Module Window: VBA : Export and save worksheets as new workbook in a new folder.
How do I copy data from multiple sheets to one sheet in Excel using macro?Using a macro to combine multiple Excel files into one. We will click on Alt + F11 and select Visual Basic Editor.. Next, we will right-click on This Workbook and select Insert, then Modules.. In the window that opens up, we will enter the code displayed below.. |
