Consolidate data from multiple worksheets into one sheet

Question

I am having trouble with writing a macro that could consolidate specific data range R10C1:R26C2 from multiple sheets in active workbook as i have to write macro physically for lets say 14 sheets as you can see from example. is there a way where the macro can pick any number of sheets within the active workbook within that data range and consolidate the data( i need to sum the data within the data range). And also i have to paste the same formula 3 times to make sure that headings of the tables (top row) and left row gets copied properly. Please help me, any help would be much appreciated.

Sub Macro15()    
    Sheets.Add

    ActiveSheet.Select
    Range("A1").Select

    Selection.Consolidate Sources:=Array("Sheet4!R10C1:R26C2", "Sheet4 (2)!R10C1:R26C2", "Sheet4 (3)!R10C1:R26C2", "Sheet4 (4)!R10C1:R26C2", "Sheet4 (5)!R10C1:R26C2", "Sheet4 (6)!R10C1:R26C2", "Sheet4 (7)!R10C1:R26C2", "Sheet4 (8)!R10C1:R26C2", "Sheet4 (9)!R10C1:R26C2", "Sheet4 (10)!R10C1:R26C2", "Sheet4 (11)!R10C1:R26C2", "Sheet4 (12)!R10C1:R26C2", "Sheet4 (13)!R10C1:R26C2", "Sheet4 (14)!R10C1:R26C2"), _
    Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

    Selection.Consolidate Sources:=Array("Sheet4!R10C1:R26C2", "Sheet4 (2)!R10C1:R26C2", "Sheet4 (3)!R10C1:R26C2", "Sheet4 (4)!R10C1:R26C2", "Sheet4 (5)!R10C1:R26C2", "Sheet4 (6)!R10C1:R26C2", "Sheet4 (7)!R10C1:R26C2", "Sheet4 (8)!R10C1:R26C2", "Sheet4 (9)!R10C1:R26C2", "Sheet4 (10)!R10C1:R26C2", "Sheet4 (11)!R10C1:R26C2", "Sheet4 (12)!R10C1:R26C2", "Sheet4 (13)!R10C1:R26C2", "Sheet4 (14)!R10C1:R26C2"), _
    Function:=xlSum, TopRow:=True, LeftColumn:=False, CreateLinks:=False

    Selection.Consolidate Sources:=Array("Sheet4!R10C1:R26C2", "Sheet4 (2)!R10C1:R26C2", "Sheet4 (3)!R10C1:R26C2", "Sheet4 (4)!R10C1:R26C2", "Sheet4 (5)!R10C1:R26C2", "Sheet4 (6)!R10C1:R26C2", "Sheet4 (7)!R10C1:R26C2", "Sheet4 (8)!R10C1:R26C2", "Sheet4 (9)!R10C1:R26C2", "Sheet4 (10)!R10C1:R26C2", "Sheet4 (11)!R10C1:R26C2", "Sheet4 (12)!R10C1:R26C2", "Sheet4 (13)!R10C1:R26C2", "Sheet4 (14)!R10C1:R26C2"), _
    Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

    ActiveWindow.SmallScroll Down:=-228
    Columns("A:A").ColumnWidth = 23.88
    Columns("A:A").ColumnWidth = 41.25
    Columns("A:A").ColumnWidth = 47.88
    Columns("B:B").ColumnWidth = 16.5
End Sub

Your `Selection.Consolidate` methods are threefold! Any reason? To that affect, `Consolidate` uses an Array, so if your references are manually entered, put them in an Array variable, so you can easily pass it along within your Routine, or other Routines. — J VBA, Mar 13 '19 at 02:37
And, `Dim` all your needed variables, and don't use `Selection` *unless* you're going to pick your ranges manually. — J VBA, Mar 13 '19 at 02:39
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • I recommend always to activate `Option Explicit`: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. — Pᴇʜ, Mar 13 '19 at 07:12
thank you and can you please advise how can i get to Dim sheets as at times there can be more than 300 sheets that i would have to consolidate the same array R10C1:R26C2. — syed, Mar 13 '19 at 07:24

J VBA · Answer 1 · 2019-03-13T02:54:39.893

0

I will try and answer here, but please fill in gaps for your 'paste' ask.

Option Explicit

Sub Macro15()

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim arrRange()

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

arrRange = Array("Sheet4!R10C1:R26C2", "Sheet4 (2)!R10C1:R26C2", "Sheet4 (3)!R10C1:R26C2", _
"Sheet4 (4)!R10C1:R26C2", "Sheet4 (5)!R10C1:R26C2", "Sheet4 (6)!R10C1:R26C2", "Sheet4 (7)!R10C1:R26C2", _
"Sheet4 (8)!R10C1:R26C2", "Sheet4 (9)!R10C1:R26C2", "Sheet4 (10)!R10C1:R26C2", "Sheet4 (11)!R10C1:R26C2", _
"Sheet4 (12)!R10C1:R26C2", "Sheet4 (13)!R10C1:R26C2", "Sheet4 (14)!R10C1:R26C2")

Set ws2 = wb.Sheets.Add

With ws2
    .Range("A1").Consolidate arrRange, xlSum, True, True, False
End With

Application.ActiveWindow.SmallScroll Down:=-228
ws.Columns("A:A").ColumnWidth = 23.88
ws.Columns("A:A").ColumnWidth = 41.25
ws.Columns("A:A").ColumnWidth = 47.88
ws.Columns("B:B").ColumnWidth = 16.5

End Sub

edited Mar 13 '19 at 02:54

answered Mar 13 '19 at 02:49

J VBA

178
2
5

Thank you, the reason the "Selection.Consolidate methods are threefold!" due to the fact that i wanted "Department Column" to be copied when table gets consolidated i don't know as the R10C1:R26C1 are all headings that repeat without changes and its only R10C2:R26C2 that changes in every sheet (numbers that need to be consolidated,) so i did not know how to run the routine. I have run this macro but it seems not to copy "R10C1"- (which is the heading "department") other heading in the column from R11C1 till R26C1 have all been copied. – syed Mar 13 '19 at 06:20
and how can i get to Dim sheets as at times there can be more than 300 sheets that i would have to consolidate the same array R10C1:R26C2. i will really appreciate your help as you would know i have very basic vba skills – syed Mar 13 '19 at 06:26

Consolidate data from multiple worksheets into one sheet

1 Answers1