Why Can't I paste special > Values in my macro?

[G0liath02]

Hi All,
For some reason I'm unable to paste special --> Values in my macro so I can keep the formatting where I'm pasting it.

I used the macro recorder to get the code but when for some reason it doesn't work. However if I use a normal paste, it works fine.

Here is the code:

Sub Copy_to_Filled()
'
' Copy_to_Filled Macro
 
'
' This macro automatically cuts the appropriate fields and pastes it in the filled reports sheet.
Dim rownum As Single
rownum = ActiveCell.Row
Range("A" & rownum & ":N" & rownum & "").Select
    'ActiveCell.Offset(0, -8).Range("A1:N1").Select
    Selection.Cut
    Sheets("Proto - Filled Roles").Select
 
    'goes to the row just below the last filled row
ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).Select
 
'Pastes the selection into the filled roles sheet
'I've commented out the ActiveSheet.Paste below to try the paste special
 
'ActiveSheet.Paste
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 'go back and remove the now blank row
 
 Sheets("Proto - Open Roles").Select
 Selection.Delete Shift:=xlUp
 'return to the Filled Roles Page
 Sheets("Proto - Filled Roles").Select

Does anyone know what could be causing this?

 

[mvptomlinson]

Hi Goliath,

1. You should avoid using Select statements as much as possible. Really slows down macros. Also, in this case, it's likely the culprit since you're Cutting some values, and then selecting other cells/rows, and finally trying to paste the values elsewhere.

2. You can't use PasteSpecial with Cut, only with Copy. You can copy the row to the new sheet, then delete the old row.

Take a look at the revised code below and try it against your data (a copy of your data might be wise!). Because you're using the 'ActiveCell.Row' as the guide, make sure you've selected the row you intend to. If you're on cell A8 on any sheet, it will move row 8 from sheet "Proto - Open Roles" to sheet "Proto - Filled Roles".

Sub Copy_to_Filled()
Dim rownum As Long, nextRow As Long
rownum = ActiveCell.Row
nextRow = Sheets("Proto - Filled Roles").Range("A65536").End(xlUp).Row + 1
Range("A" & rownum & ":N" & rownum).Copy
Sheets("Proto - Filled Roles").Range("A" & nextRow).PasteSpecial Paste:=xlPasteValues
Sheets("Proto - Open Roles").Range("A" & rownum).EntireRow.Delete Shift:=xlUp
Sheets("Proto - Filled Roles").Activate
End Sub

 

[G0liath02]

Thanks heaps for that Tom.

Your code works and is far more efficient than what I had.

Can you recommend any good resources to become a skilled Excel VBA coder? I'll be doing a bit of this from now on and it's worth investing the time to become good at this.

 

[mvptomlinson]

Glad I could help out, G.

As for good resources - everything I've learned on how to write macros was either by using Help files (Excel, VB Editor, MSDN online, etc.) and through forums such as these. There are "VBA 101" books and websites out there that can teach the basics of syntax and some cool features, but these forums are really where the minds get together and create code that can be used in real-life situations. Book code is, well, book code. It's a decent appetizer for what VBA can really do.

Hopefully the forum mods here don't punish me for this, but I like ExcelForum.com's forum structure a bit better. Rather than having every type of Excel question in one forum, like here, over there it's compartmentalized, e.g. Excel Programming (VBA), Excel 2007, Excel Functions, etc. It might be good for you to look at their Programming forum and read through some of the posts to see some of the code suggestions (or just pick and choose the threads here that refer to macros/code).

I use both sites often, as there are a lot of smart and helpful folks. Have a great day!