copy and paste works as macro, fails in VBA

dcostello

New Member
Joined
Jun 7, 2012
Messages
10
I'm trying to understand why this bit of code works as a macro but fails in VBA. My workbook has 3 sheets, Sheet1, Referrals, Modified. I'm trying to select and copy rows from Referrals to Modified. This code segment is what was recorded as a macro when I physically selected row 2 from the Referrals sheet, clicked Copy, selected row 2 from the Modified sheet and clicked Paste. If I run the macro, it works great. I then pasted this code into my VBA app and it fails with a runtime error 1004, Select method of Range class failed, on the Rows("2:2").Select. There is data in the row and there is no data in the receiving row.

Eventually, it will be selecting based on a lookup but I'm just trying to get it to work with a hard code right now.



Sheets("Referrals").Select
Rows("2:2").Select
Selection.Copy
Sheets("Modified").Select
Rows("2:2").Select
ActiveSheet.Paste
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try
Code:
Sub Macro1()
    Sheets("Referrals").Rows("2:2").Copy
    Sheets("Modified").Range("A2").PasteSpecial
    Application.CutCopyMode = False
End Sub
or
Code:
Sub Macro1()
    Sheets("Referrals").Rows("2:2").Copy Destination:=Sheets("Modified").Range("A2")
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,211,806
Messages
6,104,089
Members
447,893
Latest member
Hoosier605

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top