1

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

Midas
  • 13
  • 1
  • 5

2 Answers2

1

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    Thank you very much. This was a tremendous help and also let me know what I was doing wrong. Thank you again. – Midas Jul 03 '17 at 15:13
  • @Midas - Does my statement on why your macro was crashing make sense? If not I can expand more! – BruceWayne Jul 03 '17 at 15:15
  • I believe so. I think that you are saying I could have done more with the old way but I would have needed to preface my Cells with the sheet name too. Is that correct? – Midas Jul 03 '17 at 16:43
  • `Application.CutCopyMode = False` is needed so that the copied source cells remain and will not be cut? – Timo May 11 '21 at 07:59
  • @Timo it just turns off copy mode. See [here](https://stackoverflow.com/questions/17607412/what-exactly-is-the-function-of-application-cutcopymode-property-in-excel) – BruceWayne May 11 '21 at 10:10
-1

First, select your source sheet (line 1). Then select the range to copy (line 2). Execute Copy command (line 3). Select the destination sheet (line 4). Select the destination range (line 5). Then Paste (line 6). 

Sheets("Sheet1").Select
Range("A1:B3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A5:B7").Select
ActiveSheet.Paste

Simply substitute your values for the simple ranges in my example.

TomServo
  • 7,248
  • 5
  • 30
  • 47
  • Is it possible to do this without selecting each sheet? – Midas Jul 03 '17 at 14:46
  • @Midas - Yes, and it's actually preferred to not select each sheet.`Worksheets("Sheet1").Range("A1:B3").Copy Worksheets("Sheet2").Range("A5:B7")`. – BruceWayne Jul 03 '17 at 14:46
  • @Midas I cannot say for sure. I always start creating my macros by doing a dead-simple example and recording it. Then I embellish on that example to get the final result I want. But let me try one more thing... – TomServo Jul 03 '17 at 14:48
  • @JLH - I **highly** recommend reading through [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – BruceWayne Jul 03 '17 at 14:49
  • I am doing the following code and it wont work though. The only way I can get it to work is by using Select. Can someone explain why this doesn't work. `Sheets("Archived 30-Day Pipeline").Range(Cells(archived_report_row, archived_executive_column), Cells(archived_report_row, archived_executive_column + 4)).Copy _ Sheets("30-Day Pipeline").Range(Cells(report_row, report_executive_column), Cells(report_row, report_column_last))` – Midas Jul 03 '17 at 14:53