none
compile excels saved under a folder in a excel under different tabs and rename the tab with the excel name RRS feed

  • Question

  • HI..

    I want the VBA to collate multiple excels saved under a folder

    should be on a excel spreadsheet under different tabs 

    and name of the tab should be the name of the excel.

    Thanks,

    Tushar 

    Monday, June 4, 2018 6:57 AM

All replies

  • Here is a macro you can use:

    Sub CombineWorkbooks()
        Dim strFolder As String
        Dim strFile As String
        Dim strSheet As String
        Dim wbkS As Workbook
        Dim wbkT As Workbook
        On Error GoTo ErrHandler
        With Application.FileDialog(4)
            If .Show Then
                strFolder = .SelectedItems(1) & "\"
            Else
                Beep
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Application.Cursor = xlWait
        Application.DisplayAlerts = False
        Set wbkT = Workbooks.Add(xlWBATWorksheet)
        strFile = Dir(strFolder & "*.xls*")
        Do While strFile <> ""
            Set wbkS = Workbooks.Open(strFolder & strFile)
            wbkS.Worksheets(1).Copy After:=wbkT.Worksheets(wbkT.Worksheets.Count)
            strSheet = wbkS.Name
            strSheet = Left(strSheet, InStrRev(strSheet, ".") - 1)
            wbkT.Worksheets(wbkT.Worksheets.Count).Name = strSheet
            wbkS.Close SaveChanges:=False
            strFile = Dir
        Loop
        wbkT.Worksheets(1).Delete
    ExitHandler:
        Application.DisplayAlerts = True
        Application.Cursor = xlDefault
        Application.ScreenUpdating = True
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 4, 2018 10:38 AM
  • Hi tushar,

    Do you have any issue with this thread?

    If not, I would suggest you mark the helpful reply as answer which is the way close a thread here.

    If you do, please feel free to let us know.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 7, 2018 7:20 AM