Best way of "mirror" several cell areas on one sheet to others?

[Imbecill]

I have several areas with information I want in the exact same cells in
other sheets [in the same workbook]. I first ran a macrorecording, but of
cource there is problems with that -
- It take a lot of time and power
- Difficult to do with worksheet_change or worksheet_deactivate without loop
because you need selections in the different sheets and that make events.

I have tried Function ='Sheet1'!B34 in cell Sheet2 B34 because it run on a
more effective code and not on a vb overlap. In that case you need a
=IF('Sheet1'!B34="";"";='Sheet1'!B34) to handel all empty cells.

BUT it will be a lot of links and if you move 'Sheet1'!B34 it will cause a
#Reference value in Sheet2 B34.

All possible forms, formulas and formats should be mirrored!

Folks, what should I do ???? I trust in you...

 

[Arvi Laanemets]

Hi

I have several areas with information I want in the exact same cells in
other sheets [in the same workbook]. I first ran a macrorecording, but of
cource there is problems with that -
- It take a lot of time and power
- Difficult to do with worksheet_change or worksheet_deactivate without loop
because you need selections in the different sheets and that make events.

I have tried Function ='Sheet1'!B34 in cell Sheet2 B34 because it run on a
more effective code and not on a vb overlap. In that case you need a
=IF('Sheet1'!B34="";"";='Sheet1'!B34) to handel all empty cells.

BUT it will be a lot of links and if you move 'Sheet1'!B34 it will cause a
#Reference value in Sheet2 B34.

Only when you delete it. The formula is adjusted automatically, whenever you
rename the source sheet, and even when you move it to new file, save it and
close.

When you want the formula not to return error when source sheet is missing,
then use
=IF(ISERROR(IF('Sheet1'!B34="";"";='Sheet1'!B34));"";IF('Sheet1'!B34="";"";=
'Sheet1'!B34))
but I don't see many situations where you'll need this.
Or before ou delete the source sheet, convert links to values
(PasteSpecial.Value)

All possible forms, formulas and formats should be mirrored!

With links, only values are returned. When there is a formula in source
cell, the the value returned by formula is mirrored.
About formats - do you really need that formats are changing automatically?
Then you don't avoid a lot of VBA. Otherwise, to create a sheet as replica
of original one, right-click on sheet's tab, select Move or copy', check
'Create a copy', determine destination and press OK. After this rename the
new sheet (or save workbook) and clear all abundant (p.e. user entries) from
new sheet(, and in your case insert links to original sheet).