No announcement yet.

Combine,sort and update from multiple sheets

  • Filter
  • Time
  • Show
Clear All
new posts

  • Combine,sort and update from multiple sheets


    I've been searching persistently and for long time without success but the closer solution (link below) it was far from what i wanted.

    I have soccer fixtures workbooks which i update weekly with recent results and statistics. Each workbook is a country and it has 3 or 4 sheets that represents the division-levels of the particularly country. The structure of spreadsheets is like this:

    BUND1: A2:G309 range is the fixed data with dates, weekdays, teams etc.and G2:AC309, AN2:AO309 is the configurable range.
    BUND2: Same as the above
    3Liga: Same columns in same order but more rows
    DFBCup: Same columns in same order but fewer rows.

    I wonder if it is possible to merge the 3-4 sheets to one new sheet in the same workbook with the following criteria:
    A. Checking the G column (the first with pending data) from all the sheets and exclude the non played matches from merging.
    B. Updating master sheet every time i entering data.
    C. I only need values (no formulas, no formatting)
    D. Combined data could be auto sorted (not critical)

    Thanks in advance
    Attached Files
    <p>For all general questions relating to Excel but not including VBA or formulas.</p>
    Last edited by StephenR; October 4th, 2017, 19:24.

  • #2

    Hi again.
    After some research i finally found that code

    Sub AssembleMasterData()
    Dim ws As Worksheet
    Dim LR As Long      'used to get the last row of data on each data sheet
    With Sheets("Master")                   'put the name of your master sheet here
        .UsedRange.Offset(1).ClearContents  'remove data, leave titles in row 1
        For Each ws In Worksheets
            If ws.Name <> .Name Then        'skip the master sheet, use all others
                LR = ws.Range("H" & ws.Rows.Count).End(xlUp).Row
                ws.Range("B2:AC" & LR).Copy .Range("B" & .Rows.Count).End(xlUp).Offset(1)
                ws.Range("AN2:AO" & LR).Copy .Range("AD" & .Rows.Count).End(xlUp).Offset(1)
            End If
        Next ws
    End With
    End Sub
    It copies the specific ranges (B2:AC and AN2:AO) from all the worksheets to master, side by side,
    but only the rows that has data in column H.
    Two more modifications that must be done:
    1. Past only values and
    2. Sort the combined data by date (Column C).
    Thank you