How can I merge several worksheets into one.

[Celticshadow]

Hi Forum

I have a several workbooks with numerous sheets in them with horse racing results in each sheet for each days racing for that particular month. Each work book is named by month ie May Results and each sheet contained within is labelled by date in the below format

results_2022-5-1
results_2022-5-2
results_2022-5-3
results_2022-5-4

etc right up to results_2022-5-31

Each sheet has the same amount of columns (with same headers) but vary in length of rows. What I would like forum to help with is to merge/combine all the individually dated sheets into one master sheet descending in date order from the 1st to 31st. Any help with a macro would be much appreciated if that is the only way to do it. Many thanks in advance.

Regards

 

[Celticshadow]

Hi Forum

I have attached a screen capture of the excel file showing all the sheets/tabs with the first sheet/tab open. Hopefully this may make my request a little clearer in that I would like to merge all those sheets into one master sheet starting in date order with the 1st May. Many thanks.

Regard

 

[Logit]

Using the following macro ... first create a new tab and name it "Master List".

Using the first row in tab Master List, starting in A1 ... enter the Column Headers "Race Date", "Race Time", "Track", etc. You can copy and paste the entire Row 1 as a convenience to you.

Copy and paste the following macro into a Module :

Public Sub CombineDataFromAllSheets()

    Dim wksSrc As Worksheet, wksDst As Worksheet
    Dim rngSrc As Range, rngDst As Range
    Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
    
    'Notes: "Src" is short for "Source", "Dst" is short for "Destination"
    
    'Set references up-front
    Set wksDst = ThisWorkbook.Worksheets("Master List")
    lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
    lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!
    
    'Set the initial destination range
    Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
    
    'Loop through all sheets
    For Each wksSrc In ThisWorkbook.Worksheets
    
        'Make sure we skip the "Import" destination sheet!
        If wksSrc.Name <> "Master List" Then
            
            'Identify the last occupied row on this sheet
            lngSrcLastRow = LastOccupiedRowNum(wksSrc)
            
            'Store the source data then copy it to the destination range
            With wksSrc
                Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
                rngSrc.EntireRow.Copy Destination:=rngDst
            End With
            
            'Redefine the destination range now that new data has been added
            lngDstLastRow = LastOccupiedRowNum(wksDst)
            Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
            
        End If
    
    Next wksSrc

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
        End With
    Else
        lng = 1
    End If
    LastOccupiedRowNum = lng
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last column
'OUTPUT      : Long, the last occupied column
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Column
        End With
    Else
        lng = 1
    End If
    LastOccupiedColNum = lng
End Function

Go back to the Master List tab and insert a Command Button in cell A1. First enlarge the height of Row 1 to allow for the Command Button in the top left corner and the Column Header directly below
the Command Button. When you paste the Command Button a small window will appear with a list of macros in your workbook. Select macro CombineDataFromAllSheets and click OK.

Run the macro.

 

[alansidman]

Here is how to do it quickly with Power Query and no coding

 

[Celticshadow]

Dear Logit

Many thanks for replying. I am delighted to say that your above solution works an absolute treat and I an extremely grateful for your time and knowledge lent, you do yourself and forum proud for being so kind in taking the trouble to reply to myself and supplying a superb solution. Extremely commendable.

Regards

 

[Logit]

You are welcome.

Alansidman has provided another method using Power Query and I am certain there are many other methods available as well. Thank you for the kind words.