VBA Copy Cells from a Worksheet to a New One with Error 1004

Question

It seems I have two lines of problematic codes. Without them, the codes can run well, with them included, I will get a Run-time error '1004' Application-defined or object-defined error.

What I basically want to do is to send a command with conditions from workbook C to copy a range of cells from a workbook A to workbook B, my codes as below:

And please see the two problematic lines I marked. It looked perfectly fine to me but somehow VBA won't execute it. Can anyone explain to me why is this so?

I seem to bump into similar problems as long as I am messing with lots of workbooks, so I guess this is a general problem I have when dealing with multiple workbooks and is not limited to just copying cells...

Sub TrySaveAs()

  Dim wkb0 As Workbook
  Set wkb0 = ThisWorkbook
  Dim wkb1 As Workbook
  Dim wkb2 As Workbook

  Dim i As Integer
  i = 2

  Do Until IsEmpty(wkb0.Worksheets("Sheet2").Cells(i, 1))

    Dim full_path As String
    full_path = C:\something\something.xlsx

    Set wkb1 = Workbooks.Open(Filename:=somename)
    wkb1.SaveAs Filename:=full_path

    Set wkb2 = Workbooks.Open(Filename:=someothername)
    Dim last_row As Integer
    last_row = wkb2.Worksheets("RAW").Range("A1").End(xlDown).Row
    wkb2.Worksheets("RAW").Range(Cells(1, 1), Cells(last_row, 5)).Copy <--------problematic
    wkb1.Worksheets("Sheet1").Range(Cells(1, 1), Cells(last_row, 5)).Paste <--------problematic
    wkb2.Close SaveChanges:=False

    wkb1.Close SaveChanges:=True

    i = i + 1

  Loop

End Sub

Check your wkb2 to see if it opens successfully, and if the worksheet's name are correct / exist / not hidden. Also, full_path's value needs "" — Alex
i think they are OK. i just crossed them out here to avoid complications... — Bosco Tsin

Rusan Kax Rusan Kax · Accepted Answer · 2014-12-23T21:19:44

You could do

wkb2.Worksheets("RAW").Range(Cells(1, 1).Address & ":" & Cells(last_row, 5).Address).Copy

and

wkb1.Worksheets("Sheet1").Range(Cells(1, 1).Address & ":" & Cells(last_row, 5).Address).PasteSpecial

edit

To understand better why take a look at this, with the comments

Sub hello()
Dim rr1 As Range
Dim rr2, rr3 As Range

Debug.Print Range(Worksheets(1).Cells(1, 1), Worksheets(1).Cells(3, 3)).Address(External:=False)
Set rr1 = Range(Worksheets(1).Cells(1, 1), Worksheets(1).Cells(3, 3))
Debug.Print rr1.Parent.Name & "'!" & rr1.Address(External:=False)
MsgBox rr1.Parent.Name & "'!" & rr1.Address(External:=False)
'notice it says "Sheet1'!$A$1:$C$3

Worksheets(2).Activate
Set rr2 = Cells(1, 1) 'if we dont qualify Cells, with a range object, it defaults to cells of ActiveSheet
Set rr3 = Cells(3, 3)

Debug.Print Range(rr2, rr3).Parent.Name & "'!" & Range(rr2, rr3).Address(External:=False)
MsgBox Range(rr2, rr3).Parent.Name & "'!" & Range(rr2, rr3).Address(External:=False)
'notice it says "Sheet2'!$A$1:$C$3, because that is the current ActiveSheet

Worksheets(3).Activate
Set rr2 = Cells(1, 1) 'if we dont qualify Cells with a range object, it defaults to cells of ActiveSheet
Set rr3 = Cells(3, 3)
Debug.Print Range(rr2, rr3).Parent.Name & "'!" & Range(rr2, rr3).Address(External:=False)
MsgBox Range(rr2, rr3).Parent.Name & "'!" & Range(rr2, rr3).Address(External:=False)
'notice it says "Sheet3'!$A$1:$C$3, because that is the current ActiveSheet

On Error GoTo theError
Debug.Print Worksheets(1).Range(rr1, rr2).Address
MsgBox Worksheets(1).Range(rr1, rr2).Address
theError: Debug.Print "Error because ranges rr1 and rr2 are cells of Worksheet(3), not Worksheet(1)"

Debug.Print Range(Cells(1, 1), Cells(3, 3)).Parent.Name & "'!" & _
Range(Cells(1, 1), Cells(3, 3)).Address(External:=False)
MsgBox Range(Cells(1, 1), Cells(3, 3)).Parent.Name & "'!" & _
Range(Cells(1, 1), Cells(3, 3)).Address(External:=False)
'notice it says "Sheet3'!$A$1:$C$3, because that is the current ActiveSheet
'Moral of story you cannot do Worksheets(1).range(cells(a,b),cells(c,d)) because
'cannot guarantee Cells refer to Worksheet(1)


'If you really want to refer to unqualified Cells, one way is to mimic the Excel
'range syntax by grabbing the cell address
Debug.Print Worksheets(1).Range(Cells(1, 1).Address & ":" & Cells(3, 3).Address).Parent.Name & "'!" & _
Worksheets(1).Range(Cells(1, 1).Address & ":" & Cells(3, 3).Address).Address(External:=False)

MsgBox Worksheets(1).Range(Cells(1, 1).Address & ":" & Cells(3, 3).Address).Parent.Name & "'!" & _
Worksheets(1).Range(Cells(1, 1).Address & ":" & Cells(3, 3).Address).Address(External:=False)

End Sub

VBA Copy Cells from a Worksheet to a New One with Error 1004

3 Answers