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!
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!