Excel-for-Mac VBA Macro for Paste Special ⇒ Values & Source Formatting.

Question

I'm using Excel for Mac 16.77.1. I'd like to create a VBA macro to do Paste Special ⇒ Values & Source Formatting (see screenshot at bottom).

Specifically, I will first manually copy the selection, and click on the starting destination cell. Then I'd like the macro to take care of the pasting. Here's what I have thus far, which I know doesn't work. What am I missing?

Sub PasteValuesAndSourceFormats()

xlPasteValuesAndNumberFormats

xlPasteFormats

End Sub

In searching online, I've been able to find code for copying the values from one specified range and pasting them into another. But that's too specific for my purposes. I just want a macro that executes the following generic actions, so that I can do this with a keyboard shortcut instead of mouse clicks:

Answer 1

You may record the macro first.

Quick start: Create a macro - Microsoft Support

Then search the object in VBA reference.

Excel Visual Basic for Applications (VBA) reference | Microsoft Learn

Ah, that's a clever idea!

Here's what it gave me when I recorded the macro:

The problem is the Range("C8").Select statement, which causes the macro to always put the pasted values in the specific cell I chose when I recorded the macro (in this case, C8), which is not what I want (and why the macro doesn't work). The solution is simple: Remove that statement:

Answer 2

You may record the macro first.

Quick start: Create a macro - Microsoft Support

Then search the object in VBA reference.

Excel Visual Basic for Applications (VBA) reference | Microsoft Learn

You may also ask the function you want on GPT.

Answer 3

Here's the corrected VBA code that should work for you:

XlPasteType enumeration (Excel) | Microsoft Learn

=================================

Sub PasteValuesAndSourceFormats()

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

End Sub

=================================

This code will paste the copied selection as values and source formatting into the currently selected cell.

Answer 4

Here's the corrected VBA code that should work for you:

XlPasteType enumeration (Excel) | Microsoft Learn

=================================

Sub PasteValuesAndSourceFormats()

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

End Sub

=================================

This code will paste the copied selection as values and source formatting into the currently selected cell.

Thanks for the quick reply!

That's different from what I was looking for—your code pastes everything (including the formulas), while I want just the values and source formatting. However, using your very helpful syntax as a starting point, I was able to get a working macro:

=================================

Sub PasteValuesAndSourceFormats()
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

=================================

This seems to run fine, but is it the technically correct way to write it? I just want to ensure it's not going to fail in unexpected ways.

Also, can you recommend a good beginner's reference guide to writing these macros?