Methods of Copy/Pasting between different workbooks

[ChemicalPaulie]

Hi. I've been using VBA for about 2 weeks and I'm struggling to find a method of copy/pasting between workbooks into a specific range. I've tried multiple different methods I've found on google and from other threads on this forum, but it always fails and gives me a 1004 error at the same line. My code is:
<code style="box-sizing: border-box; font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857em; padding: 0px !important; background-color: transparent;">Private Sub CommandButton2_Click()

Dim Mastersheet As Workbook
Dim Dest1 As Workbook
Dim Dest1_path As String
Dim Data1 As Range

Set Mastersheet = ThisWorkbook
Set Data1 = Mastersheet.Sheets("Wave1").Range("E4:H461, K4:N461, Q4:T461, W4:z461, AC4:AF461, AI4:AL461, AO4:AR461, AU4:AX461, BA4:BD461, BG4:BJ461, BM4:BP461, BS4:BV461, BY4:CB461, CE4:CH461, CK4:CN461, CQ4:CT461, CW4:CZ461, DC4:DF461, DI4:DL461, DO4:DR461")

Data1.Select
Selection.Copy

Dest1_path = "E:\template\folder\test1.xlsx"
Set Dest1 = Workbooks.Open(Dest1_path)

</code><code style="box-sizing: border-box; font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857em; padding: 0px !important; background-color: transparent;">****(Now here's when things start to go wrong)****</code><code style="box-sizing: border-box; font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857em; padding: 0px !important; background-color: transparent;">
Dest1.Sheets(1).Range(Cells(1, 1), Cells(458, 80)).Paste
Dest1.Save
End Sub
</code>I've also tried substituting that 3rd-to-last line with other things but it still won't work, such as
<code style="box-sizing: border-box; font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857em; padding: 0px !important; background-color: transparent;">Dest1.Sheets(1).Range(Cells(1, 1), Cells(458, 80)) = Data1
</code>or
<code style="box-sizing: border-box; font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857em; padding: 0px !important; background-color: transparent;">Dest1.Sheets(1).Range("A1:CB458").Paste
</code>I expect it's something very simple, but anyone know where I'm going wrong here? Also as a followup, I tried doing this without a command button, but then the code would fail at the select/copy step if I was on the specific sheet where the "Data1" range is located. I suspect it was something to do with activating the sheet, but adding the line "Mastersheet.Sheets("Wave1").Activate didn't help.
Thanks!

 

[Norie]

Does this work?

Dim Mastersheet As Workbook
Dim Dest1 As Workbook
Dim Dest1_path As String
Dim Data1 As Range

    Set Mastersheet = ThisWorkbook
    Set Data1 = Mastersheet.Sheets("Wave1").Range("E4:H461, K4:N461, Q4:T461, W4:z461, AC4:AF461, AI4:AL461, AO4:AR461, AU4:AX461, BA4:BD461, BG4:BJ461, BM4:BP461, BS4:BV461, BY4:CB461, CE4:CH461, CK4:CN461, CQ4:CT461, CW4:CZ461, DC4:DF461, DI4:DL461, DO4:DR461")

    Dest1_path = "E:\template\folder\test1.xlsx"
    Set Dest1 = Workbooks.Open(Dest1_path)

    Data1.Copy Dest1.Sheets(1).Cells(1, 1)

    Dest1.Save

 

[ChemicalPaulie]

Worked like a charm! Still not sure what was wrong with my own code but I probably just need some practice to get my head around the syntax.

Thanks!

 

[Norie]

This would only work if the sheet referred to by Dest1.Sheets(1) was active.

Dest1.Sheets(1).Range(Cells(1, 1), Cells(458, 80)).Paste

You can get round that by ensuring Cells also have the Dest1.Sheets(1) reference.

Here's one way,

Dest1.Sheets(1).Range(Dest1.Sheets(1).Cells(1, 1), Dest1.Sheets(1).Cells(458, 80)).Paste

and this is a bit more concise.

With Dest1.Sheets(1)
    .Range(.Cells(1, 1), .Cells(458, 80)).Paste
End With

However when pasting you only really need to specify the top left corner of the range you want to paste to, eg Dest1.Sheets(1).Cells(1, 1) as in this code.

    Data1.Copy Dest1.Sheets(1).Cells(1, 1)