Simple Macro to Paste Comments Only

[datadrvn]

I'm trying to create a macro to simply paste the cell comments of whatever cell I've just "copied" into to the clipboard to whatever cell I've currently selected.

In Excel 2007, there is a button I can place on the toolbar to Paste-Values but there is no equivelant for Paste-Comments. I'm tired of always having to go through the Paste-Special menu.

The VBA line I've tried is:

Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Any help would be appreciated.

 

[GlennUK]

Does that macro work?. It looks OK to me.

 

[datadrvn]

Hi, Glenn...thanks for the speedy reply. I apologize for not being clearer in the original post. The command I'm using doesn't work. I get a "Run Time error '1004': PasteSpecial method of Range Class failed" error.

Wayne

 

[GlennUK]

Put a button on the QAT for it, and try again.

 

[datadrvn]

What??? Are you kidding me? Wow...now I feel stupid. Yes...it worked. Can you share your thoughts on why? It seems odd that running the macro from the Developer ribbon doesn't work and yet executing the button from the QAT does.

Otherwise...thank you very much. I never would have thought of that. I appreciate your help.

Wayne

 

[GlennUK]

Ha ha, glad that worked.

I'd guess that going via the Developer Ribbon Excel clears all buffers before running ... just a guess.

My pleasure!