Copy the formatting of one cell to other cell

  • Thread starter Thread starter Subodh
  • Start date Start date
S

Subodh

I have a cell in sheet1.
I want to copy the cell formatting to other cell in other sheet
Sheet2.
=Sheet1!A1 won't work as it will just copy the cells value but not
formatting.
How can i copy the formatting using the VBA Code?
Thanks in advance.
 
Hi

Look at this:

Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Regards,
Per
 
Record a macro when you do a copy|paste special|formats and you'll have the
syntax.
 
Did you want to copy the values also? If not, will there be other values in
the destination cells?
 
Did you want to copy the values also? If not, will there be other values in
the destination cells?

--
Rick (MVP - Excel)






- Show quoted text -
Click to expand...
The above code works to copy the formatting but not the values.
I want to copy the values also to the destination cell.
Also, I want the code to run whenever the change is made in the cell
A1 of Sheet1.
 
You didn't say which "other cell" on Sheet2 you wanted to copy A1 from
Sheet1 to, so I guessed at C3 (change that as desired)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
Target.Copy Worksheets("Sheet2").Range("C3")
End If
End Sub

This is event code and must go in Sheet1's code window. To do that,
right-click the tab at the bottom of Sheet1, select View Code from the popup
menu that appears and then copy/paste the above code into the code window
that appeared. Now, whenever you go back to Sheet1 and (manually, not
through a formula) change the value in A1, C3 on Sheet2 will have the same
value in it with the same formatting.

--
Rick (MVP - Excel)


Did you want to copy the values also? If not, will there be other values
in
the destination cells?

--
Rick (MVP - Excel)






- Show quoted text -
Click to expand...
The above code works to copy the formatting but not the values.
I want to copy the values also to the destination cell.
Also, I want the code to run whenever the change is made in the cell
A1 of Sheet1.
 
Back
Top