Merge multiple worksheets in one workbook RRS feed

  • General discussion

  • Hi there,

    I am using the following VBA code for merging the worksheets in one workbook and macro work fine but the problem is that my excel sheets are arranged in alphabetical order as per the months in one folder and after running the macro, the workbook showing sheets as Apr1990, Apr1991, Apr1992,...Apr2019, Aug1990,....). I need the sheets as per Year like Jan1990, Feb1990, Mar1990, ..... Dec2019. 

    is there any thing we can add in the below VBA and it pick those sheets in order of years?

    Sub GetSheets()
    path = "I:\Data recollection oldest share class\Estimatesmerge\"
    fileName = Dir(path & "*.xlsx")
    Do While fileName <> ""
     Workbooks.Open fileName:=path & fileName, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
     Next Sheet
     fileName = Dir()
    End Sub

    Monday, December 7, 2020 7:16 AM

All replies

  • Run the following macro after executing GetSheets (or call it at the end of GetSheets):

    Sub SortMonths()
        Dim y As Long
        Dim m As Long
        Application.ScreenUpdating = False
        On Error Resume Next
        For y = 1990 To 2019
            For m = 1 To 12
                Worksheets(MonthName(m, True) & y).Move After:=Worksheets(Worksheets.Count)
            Next m
        Next y
        Application.ScreenUpdating = True
    End Sub

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

    Monday, December 7, 2020 12:02 PM
  • Hello Hans - You are an expert. 

    much appreciated for your prompt responses and help on this.



    Tuesday, December 8, 2020 9:03 PM