Help with .consolidate Command using VBA

keith_shoaf

New Member
Joined
Oct 8, 2014
Messages
39
Hello Everyone! I am trying to use the .consolidate function of excel using VBA. I get it to work when I use the following code (with Combined_TBs = workbook.name):

ActiveSheet.Range("A1").Select
Selection.Consolidate Sources:=Array("'[" & Combined_TBs & "]DDRS TB #1'!R8C2:R242C32", "'[" & Combined_TBs & "]DDRS TB #2'!R8C2:R97C32", "'[" & Combined_TBs & "]DDRS TB #3'!R8C2:R213C32"), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

This works if I am trying to consolidate the 3 worksheets in this case (DDRS TB #1, DDRS TB #2, DDRS TB #3). However, I may or may not have more/less than 3 worksheets that need to be consolidated. I have tried to assign a variable to equate to a string that contains all the worksheet names and various ranges. I then try to use that variable (Final_Con_Rng) in the command like this:
Selection.Range("A1").Consolidate Sources:=Array(Final_Con_Rng), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Final_Con_Rng = "'[Book32]DDRS TB #1'!R8C2:R235C32", "'[Book32]DDRS TB #2'!R8C2:R90C32", "'[Book32]DDRS TB #3'!R8C2:R206C32"

Any help on how I can get the .consolidate to work using VBA that accounts for a different amount of worksheets each time it is run?

Any help on this is GREATLY appreciated!! Thanks everyone!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
915
Does this do what you're after

Code:
Final_Con_Rng = "'[Book32]DDRS TB #1'!R8C2:R235C32,'[Book32]DDRS TB #2'!R8C2:R90C32,'[Book32]DDRS TB #3'!R8C2:R206C32"
Selection.Range("A1").Consolidate Sources:=Split(Final_Con_Rng, ","), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
 

keith_shoaf

New Member
Joined
Oct 8, 2014
Messages
39
Does this do what you're after

Code:
Final_Con_Rng = "'[Book32]DDRS TB #1'!R8C2:R235C32,'[Book32]DDRS TB #2'!R8C2:R90C32,'[Book32]DDRS TB #3'!R8C2:R206C32"
Selection.Range("A1").Consolidate Sources:=Split(Final_Con_Rng, ","), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Unfortunately, it does not. I am using a loop to compile each of the sections of Final_Con_Rng. When I use is in the .consolidate command, it does not transfer properly for some reason. For example:

When I just compile Final_Con_Rng in loop and then input in consolidate command, I get this: '"''[Book30.xls]DDRS TB #1'''!R8C2:R235C32", "'[Book30]DDRS TB #2'!R8C2:R90C32", "'[Book30]DDRS TB #3'!R8C2:R206C32" in the actual worksheet.

When I manipulate the string by removing the first ", I get: R8C2:R235C32", "'[Book31]DDRS TB #2'!R8C2:R90C32", "'[Book31]DDRS TB #3'!R8C2:R206C32" in the actual worksheet.

I tried one more thing (can't remember what I did...), I get: '[Book32]DDRS TB #1'''!R8C2:R235C32", "'[Book32]DDRS TB #2'!R8C2:R90C32", "'[Book32]DDRS TB #3'!R8C2:R206C32" in the actual worksheet.

None of those work. Notice how the '! gets changed to '''! in the first reference section. I know the format needs to be specific, but can't seem to get it.
 

keith_shoaf

New Member
Joined
Oct 8, 2014
Messages
39
Here is my full code for this procedure now. I have now tried to use an Array to insert variables into .consolidate command.

FolderPathSel_DDRS_TB = "C:\Users\keith_shoaf\Desktop\UTB to ATB - Cleveland\2017 03 Dec\Working Files\DDRS Trial Balances"

DDRS_TB_File_Path = FolderPathSel_DDRS_TB & "\*.xls"
DDRS_TB_File_Nm = Dir(DDRS_TB_File_Path)

Workbooks.Add
Combined_TBs = ActiveWorkbook.Name

wksht_cnt = 1
Do Until DDRS_TB_File_Nm = vbNullString
If Not DDRS_TB_File_Nm = Combined_TBs Then
Workbooks.Open Filename:=FolderPathSel_DDRS_TB & "" & DDRS_TB_File_Nm
ActiveSheet.Name = "DDRS TB #" & wksht_cnt
Sheets("DDRS TB #" & wksht_cnt).Copy After:=Workbooks(Combined_TBs).Sheets(Workbooks(Combined_TBs).Sheets.Count)
Workbooks(DDRS_TB_File_Nm).Close False
Nb_Files_Merge = Nb_Files_Merge + 1
wksht_cnt = wksht_cnt + 1
DDRS_TB_File_Nm = Dir()
End If
Loop
nxt = 1
arr_cnt = 0
For Each Worksheet In Workbooks(Combined_TBs).Worksheets
If WorksheetFunction.CountA(Worksheet.Cells) = 0 And ActiveWorkbook.Sheets.Count > 1 Then
Worksheet.Delete
Else
Worksheet.Activate
ThisSht = ActiveSheet.Name
LastRow = Worksheet.Range("A8").CurrentRegion.Rows.Count 'count number of rows in main body of data
LastColumn = Worksheet.Range("A8").CurrentRegion.Columns.Count 'count number of columns in main body of data

ReDim Consolidate_Range_Array(arr_cnt)
Consolidate_Range_Array(arr_cnt) = """'[" & Combined_TBs & "]" & ThisSht & "'!R8C2:R" & LastRow & "C32"""
Debug.Print Consolidate_Range_Array(arr_cnt)

arr_cnt = arr_cnt + 1
nxt = nxt + 1
End If
Next Worksheet
Sheets.Add After:=Workbooks(Combined_TBs).Sheets(Workbooks(Combined_TBs).Sheets.Count)
ActiveSheet.Name = "Consolidated DDRS-B TB"
ActiveSheet.Range("A1").Select
Selection.Range("A1").Consolidate Sources:=Consolidate_Range_Array(), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Still doesn't work.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

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
Top