Help with .consolidate Command using VBA

[keith_shoaf]

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!

 

[sericom]

Does this do what you're after

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]

Does this do what you're after

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]

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.