VBA to find and replace based on columns

[mountainman88]

I have a 'consolidation' tab with a bunch of 3D sum formulas which refernce 'tab1' and 'tab2' ie the formula in any cell looks like: =sum(tab1:tab2![cell reference])

I want to use the 'consolidation' tab as a basis to create other tabs with 3D sums that refernce difference sheets based on a table on the 'table' tab

New tab name	tab1	tab2
USA	arizona	ohio
Europe	uk	singapore
HQ	Balance Sheet	Topside

Can i get some VBA that goes down the table (lets say the table starts in cell A1 of the 'tables' tab) and duplicates the 'consolidation' tab, renames it 'new tab name', then performs a find and replace to replace tab1 and tab2 text with the corresponding items in the table.

eg. for the first line 'Consolidation' would be duplicated, renamed 'USA', then tab1 text in the 3D sum would be replaced with Arizona and the tab 2 text would be replaced with Ohio.

Continuing down the list until all 3D sum tabs in the list are created. The list of tabs can be variable in length.

Thanks!

 

[Zot]

I guess many find it is hard to understand hat you were trying to achieve here including me

 

[mountainman88]

Read it one more time! Its pretty clearly and logically laid out. Find and replace text on a function =sum(tab1:tab2![cell reference]) mentioned in the first line. There are multiple tabs to copy that are each named what's in column headed 'new tab name'.

The start and end of each 3Dsum is renamed the contents of tab 1 and tab 2 eg:

'consolidation' tab is copied, renamed 'USA', after the find and replace for tab1 and tab2 text, each 3DSUM formula on it will end up looking like =sum('arizona':'ohio'![cell reference])