Hello,
Assume that you have 2 sheets Sheet1(hidden) & Sheet2 in your workbook. Sheet1 contains :
A1 : 10
A2 : 20
A3 : =A1+A2 (displays 30)
Also assume that cell A3 is formatted as bold and red.
Case A) All sheets have similar formatting for similar cells, say A3 cell in every sheet is Bold and Red.
Unhide Sheet1.
Select all sheets simultaneously.
Appply cell formating to cells in any sheet. This will apply the formatting to cells in all sheets having same adress.
Hide Sheet1.
Topaste cell A3 from Sheet1 to cell A3 of Sheet A2, type the formula
=Sheet1!A3
in cell A3 of Sheet2.
Case B) You want to paste only value and not formatting in a cell in hidden sheet, to any cell in visible sheet.
Assume that you want to copy value in A3 of Sheet1 in A4 of Sheet2.
Type the formula
= Sheet1!A3
in cell A4 of Sheet2.
You can type any complex formula involving cells in hidden sheet (=Sheet1!A1+Sheet1!A2) in any cell of visible sheet.
But formatting will not be copied.
Case C) You want to paste both formatting and value of a cell in hidden sheet to a cell in visible sheet:
Assume that you want to paste cell A3 in Sheet1 to cell B10 in Sheet2.
Use the following macro:
Sub CopyHidden()
Sheets("Sheet1").Range("A3").Copy
With Sheets("Sheet2").Range("B10")
.FormulaR1C1 = "=Sheet1!R3C1"
.PasteSpecial Paste:=xlFormats
End With
End Sub
PS: After you run this macro once, any subsequent changes in value (but not formatting) of A3 in Sheet1 will automatically be updated in B10 of Sheet2. To update changes in formatting, run macro again.
Your feedback will be highly appreciated.
Please let know if you need any clarifications before you rate the answer,
With regards,
SanganakSakha.
Simple & Effective Solutions To Small Problems, No QuickFixes.