Macro on template workbook that will consolidate multiple worksheets regardless of worksheet name

RneedofHelp

New Member
Joined
Apr 12, 2016
Messages
1
I am attempting to set up a template workbook that will consist of multiple projects, each on its own worksheet using the same template (everything is consistence for each project worksheet)

I have been able to create a combined worksheet at the beginning which consolidates specific ranges from each project worksheet without a problem. I have attached this macro to a button so the end users simply clicks and updates that range of data. Allowing the user to manipulate individual project costs and then one click updating the combined worksheet. My problem is that once the project worksheet tabs are re-labeled, the macro won’t work.

I am very new to Excel and have gotten this far but the forum’s on the web are over my head for something like this. I recorded the macro below and if anyone can help, it would be greatly appreciated!

Sub Consolidate_Exp()
'
' Consolidate_Exp Macro
'


'
Range("D36").Select
Selection.Consolidate Sources:=Array( _
"'S:\Investor Relations\Project Investor Workbook\[Project Investor Workbook - Multiple Site Project.xlsm]Site # 1'!R36C4:R83C16" _
, _
"'S:\Investor Relations\Project Investor Workbook\[Project Investor Workbook - Multiple Site Project.xlsm]Site # 2'!R36C4:R83C16" _
, _
"'S:\Investor Relations\Project Investor Workbook\[Project Investor Workbook - Multiple Site Project.xlsm]Site # 3'!R36C4:R83C16" _
, _
"'S:\Investor Relations\Project Investor Workbook\[Project Investor Workbook - Multiple Site Project.xlsm]Site # 4'!R36C4:R83C16" _
, _
"'S:\Investor Relations\Project Investor Workbook\[Project Investor Workbook - Multiple Site Project.xlsm]Site # 5'!R36C4:R83C16" _
, _
"'S:\Investor Relations\Project Investor Workbook\[Project Investor Workbook - Multiple Site Project.xlsm]Site # 6'!R36C4:R83C16" _
), Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:= _
False
Range("I3").Select
End Sub
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,102,145
Messages
5,484,990
Members
407,478
Latest member
wsupaul

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top