Copy Range from Worksheet to another Worksheet VBA

Question

I am attempting to copy a range from one worksheet to another with VBA but am failing when attempting to paste. I have attempted several methods but no luck. I have created a sample code segment that fails on the last line for pasting. The error I receive is "Run-time error '1004': Application-defined or object-defined error." Could someone please explain why this is failing and what I could do instead?

Sub Test()
Dim report_ws As Worksheet
report_row = 2
report_executive_column = 6
report_column_last = 10
archived_report_row = 3
archived_executive_column = 6

Set report_ws = Sheets("30-Day Pipeline")
Sheets("Archived 30-Day Pipeline").Range(Cells(archived_report_row, archived_executive_column), Cells(archived_report_row, archived_report_column + 4)).Copy
Sheets("30-Day Pipeline").Range(Cells(report_row, report_executive_column), Cells(report_row, report_column_last)).Paste
End Sub

Thanks in advance,

Midas

BruceWayne BruceWayne · Accepted Answer · 2017-07-03T14:51:05

How does this work for you?

Sub Test()
Dim report_ws As Worksheet, archived_ws As Worksheet
Dim copyRng As Range, pasteRng As Range

report_row = 2
report_executive_column = 6
report_column_last = 10
archived_report_row = 3
archived_executive_column = 6

Set report_ws = Sheets("30-Day Pipeline")
Set archived_ws = Sheets("Archived 30 Day Pipeline")

With archived_ws
    Set copyRng = .Range(.Cells(archived_report_row, archived_executive_column), .Cells(archived_report_row, archived_report_column + 4))
End With

With report_ws
    Set pasteRng = .Range(.Cells(report_row, report_executive_column), .Cells(report_row, report_column_last))
End With

copyRng.Copy pasteRng

Application.CutCopyMode = False

End Sub

In your original code, you were correctly adding the worksheet to what Range() you wanted...however, you also need to do that with Cells(), in the event the Range()'s worksheet is not the active one. Also, my code above is a little verbose, but I think it's good because it's pretty explicit and you can hopefully see what's going on a little better.

Copy Range from Worksheet to another Worksheet VBA

2 Answers