Simple Paste Values and Formats Macro

dcortez

New Member
Joined
Jun 17, 2007
Messages
5
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
dcortez,

Welcome to the MrExcel board.

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

dcortez

New Member
Joined
Jun 17, 2007
Messages
5
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

New Member
Joined
Jun 17, 2007
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
dcortez,

I hope this helps:


Code:
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
CD
91*
1021
1133
1246
13510
14615
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
CD
91*
1021
1133
1246
13510
14615
15*21
Sheet1
 

dcortez

New Member
Joined
Jun 17, 2007
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,400
Messages
5,571,905
Members
412,423
Latest member
monbri9931
Top