Simple Paste Values and Formats Macro

[dcortez]

In Excel 03 a simple command for pasting values and formats was included. In Exel 07 is its missing. Only a paste values button is available on the tool bar or custom list.

So I tried to create a simple macro to paste formats and values using paste special after selecting my range.

I successfully did for for a macro that pastes values and formats and transposes.
But the very same macro without transposing does not work. Can't figure out why.

Both macros are below. After the second macro, the error message I get is shown.

Can someone out there help this macro amateur understand why I can figure out something this simple?

Doug

Sub PasteValuesDown()
'
' PasteValuesDown Macro
' Macro recorded 3/16/2008 by Unknown
'
' Keyboard Shortcut: Ctrl+m
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=True
End Sub
------------------------------------------------------------------------
Sub pastevaluesformats()
'
' pastevaluesformats Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=False

End Sub

--------

Run time error 1004
Paste Special method range failed

 

[hiker95]

dcortez,

Welcome to the MrExcel board.

Did you try recording the same macro(s) in Excel 2007, and compare the two different codes?

 

[dcortez]

I did try to create the same macro in 2007. It also gave the same error. But when I use the original macro in 2007, it works fine.
I tried editing the orginal and changed the transpose to False from True. That small change caused it to fail. Very strange.

 

[hiker95]

dcortez,

There are differences between Excel 2003 and 2007 in VBA macros.

If you want to use just two macros for both Excel 2003 and 2007, we could test what version of Excel is running, and jump to that versions code.

You would have to post the code that works in both versions, and I could create the two macros that would run in either version of Excel.


At the beginning of your posted code, enter the following without the quote marks:
["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]

 

[dcortez]

code

Sub PasteValuesDown()
'
' PasteValuesDown Macro
' Macro recorded 3/16/2008 by Unknown
'
' Keyboard Shortcut: Ctrl+m
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=True
End Sub

/code

Above is the macro in question. As you suggested I tried this macro in Excel 07 on my desktop and Excel 03 on my laptop. I discovered something new.

The macro works in both Excel versions but ONLY if I execute it with the shortcut key Ctrl+m.

If I try to execute from the RUN Macro commmand I get the Run Time Error 1004 message. This happens in both Excel 03 and 07.

I did modify the macro by changing the transpose option at the end from True to False.

The macro then allows me to paste the values without transposing. Again the revised macro works in both versions but ONLY if I use Ctrl+m. The macro run option again fails and gives the error message.

Hope this helps you and thanks for the help.

Doug

 

[hiker95]

dcortez,

I hope this helps:

Option Explicit
Sub PasteValuesDown()
'
' PasteValuesDown Macro
' Macro recorded 3/16/2008 by Unknown
'
' Macro updated 07/27/2009 by hiker95 at MrExcel.com
'
' Keyboard Shortcut: Ctrl+m
'

Dim FR As Long, FC As Long
With Selection
  .Copy
  .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False
  FR = .Row
  FC = .Column
End With
Cells(FR, FC).Select
Application.CutCopyMode = False
End Sub

Before the macro in both Excel 2003 and Excel 2007, with the range C9:D15 selected:

Excel Workbook
	C	D
9	1	*
10	2	1
11	3	3
12	4	6
13	5	10
14	6	15
15	*	21
Sheet1

After the Macro in both Excel 2003 and Excel 2007:

By executing the macro with CTRL + m

or, with ALT F8 and click on the macro name and click on RUN


Or, in Excdel 2003, click on "Tools", "Macro", "Macros..."
in the "Macros in:" box pick "This Workbook"
and click on the macro name and click on RUN

Or, in Excel 2007, click on the "Developer" icon
in the "Macros in:" box pick "This Workbook"
and click on the macro name and click on RUN


And, the result will be the same, with the first cell (upper left hand cell) in the original selected range, will be selected:

Excel Workbook
	C	D
9	1	*
10	2	1
11	3	3
12	4	6
13	5	10
14	6	15
15	*	21
Sheet1

 

[dcortez]

Hiker95:

I cut and pasted your macro into my macro module.

I copied the range of cells I wanted to value range and transpose. I then hit Ctrl-m. The macro executed, no errors, but the range of values did not appear. Just a blank cell.

My macros are working but only if I use the cntrl-m command.

I also noticed that Excel has built in command for pasting values and separate one for pasting formats. By putting those on my tool bar, I can paste ranges and values to another range with two mouse clicks.

For the transpose pasting of values and formats, I will need to use my macros.

If you have any suggestions on making your macro work, let me know. If not, I think I can live with what I now have.

Thanks,

Doug

 

[hiker95]

dcortez,

I did not understand your request - my mistake.