Data consolidation? - RePost

[gidsy]

Hi There,

I have a consolidation sheet that uses Data Condolidation to sum 5 sheets.

This works fine and I have a button that is attached to this macro...

ActiveSheet.Unprotect
Range("B7").Select
Selection.Consolidate Sources:=Array( _
"'Accounts Analysis (2)'!R7C2:R71C9" _
, _
"'Accounts Analysis (3)'!R7C2:R71C9" _
, _
"'Accounts Analysis (4)'!R7C2:R71C9" _
, _
"'Accounts Analysis (5)'!R7C2:R71C9" _
, _
"'Accounts Analysis (6)'!R7C2:R71C9" _
), Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:= _
False

The users would like to be able to change the names of the sheets that are used in the aggregation. If they change the sheet name the consolidation macro won't work.

Is there a way I can still use data consolidation and still let the users change the sheet names?

 

[gidsy]

Anyone have any ideas about this one?

 

[gidsy]

Anyone have any ideas about this?

Sorry to re-post...

TIA

 

[gidsy]

Anyone have any ideas about this?

Sorry to re-post...

TIA

 

[gidsy]

Sorry to re-post...

Still keen to hear from anyone who knows how to do this...

I have a consolidation sheet that uses Data Condolidation to sum 5 sheets.

This works fine and I have a button that is attached to this macro...

ActiveSheet.Unprotect
Range("B7").Select
Selection.Consolidate Sources:=Array( _
"'Accounts Analysis (2)'!R7C2:R71C9" _
, _
"'Accounts Analysis (3)'!R7C2:R71C9" _
, _
"'Accounts Analysis (4)'!R7C2:R71C9" _
, _
"'Accounts Analysis (5)'!R7C2:R71C9" _
, _
"'Accounts Analysis (6)'!R7C2:R71C9" _
), Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:= _
False

The users would like to be able to change the names of the sheets that are used in the aggregation. If they change the sheet name the consolidation macro won't work.

Is there a way I can still use data consolidation and still let the users change the sheet names?