copy the same format to a cell using =

[Guest]

I am trying to copy a cell from one sheet to another and I use the = function
but it doesn't copy the same format ie: bold and under line etc.

 

[JE McGimpsey]

Functions can only return values, not change cell formats.

To link formats you'd need to use VBA. For example, this will copy the
formats from cells A1:A10 in Sheet1 to cells A1:A10 in Sheet2 whenever
Sheet2 is activated. Put it in the Sheet2 code module (right-click the
Sheet2 tab and choose View Code):

Private Sub Worksheet_Activate()
With Range("A1:A10")
Worksheets("Sheet1").Range(.Address).Copy
.PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
End Sub

 

[Peo Sjoblom]

If you copy and paste special as link you can carry over the format but it
is a double paste

Copy cell you want to link to, select the cell where you want this to
happen, do edit>paste special and select paste link, then do edit>paste
special again and this time select formats



--


Regards,


Peo Sjoblom

 

[Guest]

Hi Peo

But this method doesn't change the formatting the destination cell when the
formatting in the source cell is changed, no?

I *think* that's what the OP asks for.

 

[Peo Sjoblom]

I don't know if the OP meant that and if he did it obviously won't work

 

[Guest]

JE McGimpsey,

What if the range of cells in the origin sheet (in your example, Sheet1) is
linked to a different range of cells (contiguous or non-contiguous) in the
other sheet (in your example, Sheet2)? How would your formula have to chage?

I have a workbook which tracks 92 project timelines, which each have 27
separate tasks. The top worksheet is a progam summary of the critical
deadlines and looks nothing like the 92 project worksheets. I want them
linked so that the project managers will only fill in data on their project
sheets and not have to duplicate the data entry on the summary sheet. When a
target deadline has been met and the date completed confirmed, I want the
project managers to change the format of the data to bold, underline. I
assume that your formula will solve that, except for the difference in ranges.

 

[JE McGimpsey]

You'd have to have some sort of explicit 1:1 correspondence between the
summary sheet cells and their references. There could be lots of ways to
do it - one brute force way:

With Worksheets("Summary")
Sheets("Project1").Range("B17").Copy
.Range("Z3").PasteSpecial Paste:=xlFormats
Sheets("Project22").Range("R4").Copy
.Range("L5").PasteSpecial Paste:=xlFormats
'etc
End With

 

[Guest]

Thank you for the quick repsonse. Just one more dumb question...do the B17
and R4 cells correspond to the project sheets or the summary sheet?