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

"Subodh" skrev i meddelelsen
...
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.

Record a macro when you do a copy|paste special|formats and you'll have the
syntax.

Subodh wrote:

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.

--

Dave Peterson

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

--
Rick (MVP - Excel)


"Subodh" wrote in message
...
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.

On Oct 14, 7:36*am, "Rick Rothstein"
wrote:
Did you want to copy the values also? If not, will there be other values in
the destination cells?

--
Rick (MVP - Excel)

"Subodh" wrote in message

...



I have acellin sheet1.
I want to copy thecellformattingto othercellin other sheet
Sheet2.
=Sheet1!A1 won't work as it will just copy the cells value but not
formatting.
How can i copy theformattingusing the VBA Code?
Thanks in advance.- Hide quoted text -

- Show quoted text -
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)


"Subodh" wrote in message
...
On Oct 14, 7:36 am, "Rick Rothstein"
wrote:
Did you want to copy the values also? If not, will there be other values
in
the destination cells?

--
Rick (MVP - Excel)

"Subodh" wrote in message

...



I have acellin sheet1.
I want to copy thecellformattingto othercellin other sheet
Sheet2.
=Sheet1!A1 won't work as it will just copy the cells value but not
formatting.
How can i copy theformattingusing the VBA Code?
Thanks in advance.- Hide quoted text -

- Show quoted text -
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.