Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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 "gmunro" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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. Code:
Sub PasteFormating() Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub Patrick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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 "PSKelligan" wrote in message ... 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. Code:
Sub PasteFormating() Selection.Copy Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub Hope this helps, Patrick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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! "K Dales" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create format painter
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 "JD" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format painter | Excel Worksheet Functions | |||
copy conditional format without using format painter | Excel Worksheet Functions | |||
Format Painter | Excel Discussion (Misc queries) | |||
how can I use the format painter?? | New Users to Excel | |||
format painter | Excel Programming |