VBA Consolidation

Bruce · Apr 19th, 2002, 02:59 PM

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.

daleyman · Apr 19th, 2002, 03:07 PM

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

Bruce · Apr 19th, 2002, 03:39 PM

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 ]

daleyman · Apr 19th, 2002, 03:43 PM

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.

Bruce · Apr 19th, 2002, 03:46 PM

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.

daleyman · Apr 19th, 2002, 03:47 PM

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

_________________

***DALEY**

**

[ This Message was edited by: daleyman on 2002-04-19 14:48 ]

[ This Message was edited by: daleyman on 2002-04-19 14:49 ]

daleyman · Apr 19th, 2002, 03:49 PM

okay, i see the chr(33) now, sorry. so then you should take out the chr(34) ????

daleyman · Apr 19th, 2002, 03:54 PM

sorry, am just being stupid, ignore my last statement.

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

_________________

***DALEY**

**

[ This Message was edited by: daleyman on 2002-04-19 14:55 ]

Bruce · Apr 19th, 2002, 03:56 PM

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

Bruce · Apr 19th, 2002, 03:57 PM

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.

Apr 19th, 2002, 03:07 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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 __________________ <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">*DALEY :P**</td></table>

Apr 19th, 2002, 03:43 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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. __________________ <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">*DALEY :P**</td></table>

Apr 19th, 2002, 03:49 PM	#7
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	okay, i see the chr(33) now, sorry. so then you should take out the chr(34) ???? __________________ <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">*DALEY :P**</td></table>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 02:59 PM	#1
Bruce New Member Join Date: Apr 2002 Posts: 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.

Apr 19th, 2002, 03:39 PM	#3
Bruce New Member Join Date: Apr 2002 Posts: 6	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 ]

Apr 19th, 2002, 03:46 PM	#5
Bruce New Member Join Date: Apr 2002 Posts: 6	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.

Apr 19th, 2002, 03:56 PM	#9
Bruce New Member Join Date: Apr 2002 Posts: 6	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

Apr 19th, 2002, 03:57 PM	#10
Bruce New Member Join Date: Apr 2002 Posts: 6	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.