Merge tabs from different excel macro xlsm files into one file

ablalarom · Nov 20th 2017

Hi all,

I have about 20 excel macro files xlsm all containing a tab called "Freight". I need to have a new excel file (either xlsm or xlsx, I don't care) 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).

I have tried the solution proposed in a previous thread (https://www.ozgrid.com/forum/f…e-workbooks-into-new-file) but it's not working. I have changed the path and the file type to ".xlsm".

I would really appreciate your help.

Thanks!

A.

Mumps · Nov 20th 2017

What is the full path of the folder containing the 20 files? Are the 20 files the only files in that folder or are there other files in that folder as well?

ablalarom · Nov 21st 2017

The path is D:\Freight and it contains the 20 *.xlsm files only. Thanks

Mumps · Nov 21st 2017

Open a new blank workbook. Place this macro in a regular module and save the workbook as a macro-enabled file. Run the macro.

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

Display More

ablalarom · Nov 21st 2017

Thank you so much! This worked perfectly & will save me so much time. Thanks again

Mumps · Nov 22nd 2017

You are very welcome.

Merge tabs from different excel macro xlsm files into one file

Participate now!

Share