How to merge tabs from different excel macro xlsm files into one file
Category: [Excel] Demo Available
How to merge tabs from different excel macro xlsm files into one file
Requirement:
The user has about 20 excel macro files xlsm all containing a tab called "Freight". '
The user needs to have a new excel file (either xlsm or xlsx) containing all "Freight" tabs from the 20 files, renamed in some way (because excel won't accept tabs with the same name). It could take cell B7 as name of the file (that field is unique).
The user has tried the solution proposed in a previous thread (https://www.ozgrid.com/forum/forum/h...-into-new-file) but it's not working. The user has changed the path and the file type to ".xlsm".
Solution:
Code:
Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Const strPath As String = "D:\Freight\"
ChDir strPath
strExtension = Dir("*.xlsm")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
.Sheets("Freight").Copy After:=wkbDest.Sheets(wkbDest.Sheets.Count)
ActiveSheet.Name = ActiveSheet.Range("B7").Value
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to index across multiple tabs |
| How to copy the entire sheet and paste as values - running on multiple tabs |
| How to use VBA code to colour tabs based on tab/text number |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.