How to Merge tabs from different excel macro xlsm files into one file

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".

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1195692-merge-tabs-from-different-excel-macro-xlsm-files-into-one-file

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.