Macro to create format painter

[JD]

I am trying to create a macro that when I press Ctrl+m, it would be as if I
am pressing the format painter icon. I tried recording a macro and each
time I press ctrl+m, it copies the cell, but not the format. Could someone
tell me how to create the macro to copy the format only of a cell?

Thanks Again as I am a newbie when it comes to macros/vba

JD

 

[gmunro]

This worked for me.

Range("D6").Select
Selection.Copy
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[Guest]

Hi JD,
I recorded this and if you copy (ctrl+c) and use this code, it will paste
the formatting only to the active range of cells. To get the ctrl+m added
just record a macro in your personal macro workbook and set the shortcut key
to ctrl+m and then paste the body of my code to the macro.

Sub PasteFormating()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Hope this helps,

Patrick

 

[JD]

Hi gmunro,
I added the code into vba and it only copies cell D6 into cell D8.
Unfornately, this does not work for me Basically, I am trying to make a
keyboard shortcut (ctrl m) to copy the format of a cell only. Then once I
hit ctrl m, I want to be able to just use my mouse and highlight whatever
cells I want to copy the format, just as if I hit the format painter icon.
Thanks anyway.

JD

 

[Guest]

When you copy a cell, you copy everything: contents and format. The
difference comes when pasting to a new cell, you specify you want to paste
the format. The "format painter" just automates this process. So your macro
would need to do the copy, have you specify the target cell(s), and then do a
PasteSpecial (this is what the format painter does, too). This means you
need some way of detecting when the user has chosen the cell to paste to, and
that makes it a bit more complex. You need to have a way to say (to Excel)
"I am waiting for a new cell..." and then when the user chooses a new cell,
do the paste.

There is a workbook event procedure for Workbook_SheetSelectionChange that
runs whenever the user changes the selection. So here is an outline of what
needs to happen:
1) User initiates your custom "format painter" macro
2) The macro copies the cell
3) The macro sets some sort of flag: waiting for next selection
4) The user changes the selection (activating Workbook_SheetSelectionChange)
5) the Workbook_SheetSelection procedure reads the flag that says waiting
for next selection, and recognizes that the user wants to paste the format.
So it runs code to do a PasteSpecial, then resets the flag (so it doesn't
keep pasting for every new selection)

Here is the whole deal: insert a module and use it for the code below:

Public UseMyFormatPainter as Boolean

Public Sub MyFormatPainter
Selection.Copy
UseMyFormatPainter = True
End Sub

Now go to ThisWorkbook in the Project Explorer and enter this code in the
Workbook:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If UseMyFormatPainter Then Selection.PasteSpecial xlPasteFormats
UseMyFormatPainter = False
Application.CutCopyMode = 0 ' turns "off" the dotted border around the
copied cell

End Sub

This is bare-bones and does not do any error checking, or allow multiple
targets (like when you double-click the format painter) but it shows how to
control that kind of behavior. Hope it helps.

 

[JD]

Hi Patrick
I copied the code and I couldn't get it to work. Could you give me the
exact code that I need to copy into the vba worksheet. Perhaps I am doing
something wrong. I appreciate all your help.

Thanks
JD

 

[Jim Cone]

JD,

You can use the following code, after
copying the cell(s) and selecting the paste area...

'----------------------
Sub FormatsOnly()
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub
'----------------------

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

A little bit of an "oops" in my code: change the SheetSelectionChange sub to
this:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If UseMyFormatPainter Then
Selection.PasteSpecial xlPasteFormats
UseMyFormatPainter = False
Application.CutCopyMode = 0 ' turns "off" the dotted border around the
copied cell
End If

End Sub

Otherwise, it will always turn off CutCopyMode when you change the
selection, not a desirable behavior!