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
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
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,647
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
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,647
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*
102
113
124
135
146
15*
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
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top