VBA Consolidation

Bruce

New Member
Joined
Apr 18, 2002
Messages
6
I'm trying to write a macro that will consolidate all the worksheets.

The only way I have found is to list the actual worksheet names in the consolidation array line. The files I want to use this macro for are automatically created and the names may change so I can't hard code them in.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
what are you consolidating? does each sheet have identical width list with column headers in row 1? If so, try...



Sub consolidate_lists()
For sheetno = 2 To Sheets.Count
Sheets(sheetno).Rows("2:5").Copy
Sheets(1).Rows("2:2").Insert Shift:=xlDown
Next
End Sub
 
Upvote 0
The left column has product names and the top row has a column heading. Not all the products are on every worksheet. I need a way to add up the ones that are on multiple sheets (but different rows) and include the ones that are only on one sheet.

The consolidation function does this and I have it referencing the top and left cells for headings/columns.

Here is the code I have that does not work, it doesn't like my string in the array. But, if I type the string in it is fine.

(Works if I replace the last-
Array(ConRange) with
Array("Sheet1!R11C1:R27C4", "Sheet2!R11C1:R27C4", "Sheet3!R11C1:R27C4")

*****

GrpWorkSheets = Worksheets.Count
CurWorksheet = 2

Do While CurWorksheet<= GrpWorkSheets
Worksheets(CurWorksheet).Activate
sheetname = Worksheets(CurWorksheet).Name
rangename = Chr(34) & sheetname & Chr(33) & "R11C1:R27C4" & Chr(34) & ", "
ConRange = ConRange & rangename
CurWorksheet = CurWorksheet + 1
Loop

numChar = Len(ConRange)
numChar = numChar - 2
ConRange = Left(ConRange, numChar)
Worksheets(1).Activate
Range("A11").Select
Selection.Consolidate Sources:=Array(ConRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
This message was edited by Bruce on 2002-04-19 14:42
 
Upvote 0
haven't tested your code or really scrutinised but on first view looks like you're missing an exclamation mark in...

rangename = Chr(34) & sheetname & Chr(33) & "R11C1:R27C4" & Chr(34) & ", "


...ie...

rangename = Chr(34) & sheetname & Chr(33) & "!R11C1:R27C4" & Chr(34) & ", "


...if you are going to go about it this way.
 
Upvote 0
It didn't like the actual exclamtion mark in quotes so that's the chr(33).

For testing I had it place ConRange value in a cell and then copy/pasted it into the array line and everything worked fine. It doesn't like me referencing it with ConRange for some reason.
 
Upvote 0
i have abbreviated you code down to a few lines...



For worksheetno = 2 To Worksheets.Count
conRange = conRange & Sheets(worksheetno).Name & "!R11C1:R27C4" & Chr(34) & ","
Next

conRange = Left(conRange, Len(conRange) - 1)

Sheets(1).Range("A11").Consolidate Sources:=Array(conRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False



_________________<table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">   DALEY   :)  </td></table>
This message was edited by daleyman on 2002-04-19 14:48
This message was edited by daleyman on 2002-04-19 14:49
 
Upvote 0
sorry, am just being stupid, ignore my last statement.

PS: why are you doing numChar=numChar-2, surely you only need strip one character?


_________________<table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">   DALEY   :)  </td></table>
This message was edited by daleyman on 2002-04-19 14:55
 
Upvote 0
Your coding didn't mind the "!"

So now your coding is more effiecent (THANKS!!), but same issue.

I did the same test and added a temporary line to have the ConRange go into a cell and copy/pasted that into the coding and it worked perfect...still didn't work with ConRange being in there.

----
For worksheetno = 2 To Worksheets.Count
ConRange = ConRange & Chr(34) & Sheets(worksheetno).Name & "!R11C1:R27C4" & Chr(34) & ","
Next

ConRange = Left(ConRange, Len(ConRange) - 1)
Sheets(1).Range("A11").Consolidate Sources:=Array(ConRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
 
Upvote 0
I was stripping a space I after the comma, it wasn't neccesary...just a little easier to read when viewing the string. Your coding just had the comma which is fine.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top