Consolidating multiple sheets into one

[neylon]

Hi

Just as a preface, i'm fairly competant at excel, so even if you don't have an exact solution, pointing me in the direction of a certain formula/layout/function that might work would still be very much appreciated.

My finance team pulls financial data from our accounting system into excel using Jet. At the moment they have a file set up with a tab for each of our locations, each tab is basically an output list of variable length outlining client debt & ageing (headings are identical for each location)

However, we now want to put all this information from each tab into one list that can be filtered by client/property/etc. Apparently they've previously tried to just pull everything into one tab using Jet, but our system just cant cope with this and crashes when they try. Apparently in the past we've even had external consultants to try and set it up but they failed. I'm a little confused as to why as ultimately it would only be 6 columns across <10k rows but here we are.

So the current solution is to go into each tab and manually copy & paste the lists into one tab. This takes about 30-40 minutes every day and obviously introduces the risk of human error.

The information is all there and organised, so from my experience with excel there should be some way to pull all this together automatically in less than a couple of minutes.

In summary, i have 40 tabs, each with 6 columns of data starting from row 10, all of varying length day-to-day. I need to efficiently organise all these into one 'master list'. So if 3 tabs had 10 rows each of data, the master list would have 30 rows.

any help would be much appreciated as it would save a hell of a lot of time every week.
Thanks!

 

[JackDanIce]

A macro can automate this for you, especially if the data/sheets are all in a single workbook and ideally not on a Sharepoint or Cloud based location.

If so, please can you specify:

Workbook name
If it is saved locally, a networked drive or other
The names of all the input sheets
The starting cell address within each input sheet (A10?)
Output sheet name ("Master List"?)
Destination starting cell address in output sheet
Is previous data on output sheet to be deleted before the aggregation of data from input sheets?

Assume anyone reading is blind and they need to recreate what you can see in front of you, how would you describe in an accurate way

 

[Fluff]

How about

Sub Neylon()
   Dim Ws As Worksheet, wsMaster As Worksheet
   
   Sheets.Add(Sheets(1)).Name = "Master"
   Set wsMaster = Sheets("Master")
   Sheets(2).Range("A9:F9").Copy wsMaster.Range("A1")
   For Each Ws In Worksheets
      If Not Ws.Name = wsMaster.Name Then
         Ws.Range("A10:F" & Ws.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub

This will create a new "Master" sheet Add the header from row 9 of one the sheets & then copy data from row10 downwards

 

[neylon]

A macro can automate this for you, especially if the data/sheets are all in a single workbook and ideally not on a Sharepoint or Cloud based location.

If so, please can you specify:

Workbook name
If it is saved locally, a networked drive or other
The names of all the input sheets
The starting cell address within each input sheet (A10?)
Output sheet name ("Master List"?)
Destination starting cell address in output sheet
Is previous data on output sheet to be deleted before the aggregation of data from input sheets?

Assume anyone reading is blind and they need to recreate what you can see in front of you, how would you describe in an accurate way

Hi Jack, thank you very much for responding. The information i used above was slightly simplified so please use the below:

- This excel file currently lives on sharepoint, however if necessary temporarily copying it to my desktop to run a macro is not an issue.
- Unfortunately i cant give you all the tab names as you'll be able to figure out where i work and there's some unanounced locations in there. Currently there are 41 tabs with names ranging from 3 to 22 characters.
- every list begins with the headings starting in cell G26, and 12 columns across. (first 4 are text, last 8 are numbers). The list length varies dramatically by location and also day-to-day. So ranging from 1 row to 3000+.
- 'Master List' is fine
- starting A1 including headings is fine
- yes, will need to be a fresh list every day. I can just clear this manually if it's an issue.

Another probably major detail i'm waiting to hear back on; each line has sub-lines with additional detail that are automatically hidden by Jet. I dont need this detail so have enquired as to whether this can be excluded when we run the report (they think it should be able to). If it can't would it be possible to only pull in rows i've tagged in column 13 with whatever text you think appropriate (e.g. just a 'Y')

Thank you!

 

[JackDanIce]

I can use Comp1, Comp2, Co3mp to proxy company (Sheet) names, you'll need to adapt accordingly.
I'm going to assume yes to clear all data in Master List sheet, except row 1, headers
Fixed column size of 12 per sheet and unknown row size, but first data row is G26:R26

Change sheet names and then try on a downloaded copy:

Sub Console_Main()
   
   'Create and initialise variables where possible
    Dim w           As Worksheet
    Dim wML         As Worksheet: Set wML = Sheets("Master List")
    Dim SheetNames  As Variant: SheetNames = My_Sheet_Names
    Dim x           As Long
    Dim s           As Double: s = Timer
  
    'Clear Master List
    Clear_Master wML
  
    'Loop across sheets and copy data to Master List
    For x = LBound(SheetNames) To UBound(SheetNames)
        Update_Master wML, Sheets(CStr(SheetNames(x))), lastRow(wks, 7) - 25
    Next x
  
    'Notify finish
    Finish_Macro s
  
    'Clear variables
    Erase SheetNames: Set wML = Nothing
  
End Sub

Private Function My_Sheet_Names() As Variant
'List of required sheet names
    My_Sheet_Names = Array("Comp1", "Comp2", "Comp3")
End Function

Private Sub Clear_Master(ByRef wks As Worksheet)
    'Clear Master List except header row
    Application.ScreenUpdating = False
    With wks
        .Cells(2, 1).Resize(lastRow(wks, 1) - 1, 12).Value = ""
    End With
    Application.ScreenUpdating = True
End Sub

Private Function lastRow(ByRef wks As Worksheet, ByRef xCol As Long) As Long
'Return last row number of each sheet, based on column index xCol
    With wks
        lastRow = .Cells(.rows.Count, xCol).End(xlUp).Row
    End With
End Function

Private Sub Update_Master(ByRef wML As Worksheet, wks As Worksheet, ByRef LR As Long)
'Copy from row 26, column G to end row, column R in source sheet wks to Master List
    Application.ScreenUpdating = False
    With wML
        .Cells(.rows.Count, 1).End(xlUp).Offset(1).Resize(LR, 12).Value = wks.Cells(26, 7).Resize(LR, 12).Value
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub Finish_Macro(ByRef s As Double)
'User notification
    If ActiveSheet.Name <> wML.Name Then wML.Activate
    MsgBox "Data aggregation complete" & vbCrLf & vbCrLf & "Run time: " & Round(Timer - s, 2) & " secs", vbOKOnly, "Aggregation Complete"
End Sub

 

[neylon]

I can use Comp1, Comp2, Co3mp to proxy company (Sheet) names, you'll need to adapt accordingly.
I'm going to assume yes to clear all data in Master List sheet, except row 1, headers
Fixed column size of 12 per sheet and unknown row size, but first data row is G26:R26

Change sheet names and then try on a downloaded copy:

Sub Console_Main()

   'Create and initialise variables where possible
    Dim w           As Worksheet
    Dim wML         As Worksheet: Set wML = Sheets("Master List")
    Dim SheetNames  As Variant: SheetNames = My_Sheet_Names
    Dim x           As Long
    Dim s           As Double: s = Timer

    'Clear Master List
    Clear_Master wML

    'Loop across sheets and copy data to Master List
    For x = LBound(SheetNames) To UBound(SheetNames)
        Update_Master wML, Sheets(CStr(SheetNames(x))), lastRow(wks, 7) - 25
    Next x

    'Notify finish
    Finish_Macro s

    'Clear variables
    Erase SheetNames: Set wML = Nothing

End Sub

Private Function My_Sheet_Names() As Variant
'List of required sheet names
    My_Sheet_Names = Array("Comp1", "Comp2", "Comp3")
End Function

Private Sub Clear_Master(ByRef wks As Worksheet)
    'Clear Master List except header row
    Application.ScreenUpdating = False
    With wks
        .Cells(2, 1).Resize(lastRow(wks, 1) - 1, 12).Value = ""
    End With
    Application.ScreenUpdating = True
End Sub

Private Function lastRow(ByRef wks As Worksheet, ByRef xCol As Long) As Long
'Return last row number of each sheet, based on column index xCol
    With wks
        lastRow = .Cells(.rows.Count, xCol).End(xlUp).Row
    End With
End Function

Private Sub Update_Master(ByRef wML As Worksheet, wks As Worksheet, ByRef LR As Long)
'Copy from row 26, column G to end row, column R in source sheet wks to Master List
    Application.ScreenUpdating = False
    With wML
        .Cells(.rows.Count, 1).End(xlUp).Offset(1).Resize(LR, 12).Value = wks.Cells(26, 7).Resize(LR, 12).Value
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub Finish_Macro(ByRef s As Double)
'User notification
    If ActiveSheet.Name <> wML.Name Then wML.Activate
    MsgBox "Data aggregation complete" & vbCrLf & vbCrLf & "Run time: " & Round(Timer - s, 2) & " secs", vbOKOnly, "Aggregation Complete"
End Sub

Thanks so much for this.

I'm getting an error "Compile Error: ByRef argument type mismatch" and it highlights the 'wks' in "lastRow(wks, 7)" in the 'loop across sheets' section

 

[JackDanIce]

My bad, change

lastRow(wks, 7)

to

lastRow(Sheets(CStr(SheetNames(x))), 7)

 

[alansidman]

Here is an alternative solution tutorial for combining sheets with Power Query

Combine All Sheets in a Workbook with Power Query

You will learn how to combine data in all the sheets in an Excel workbook using Power Query. You will learn the required tips and tricks.

exceleratorbi.com.au

 

[neylon]

My bad, change

lastRow(wks, 7)

to

lastRow(Sheets(CStr(SheetNames(x))), 7)

Thank you Jack

Unfortunately i now have another error; "Run-time error '9', Subscript out of range"

this is highlighted at the top
: Set wML = Sheets("Master List")

Thanks for all your help

 

[JackDanIce]

Is your sheet called “Master List”