VBA将单元格从工作表复制到新工作表，错误为1004

Question

我似乎会遇到类似的问题，只要我搞乱了很多工作簿，所以我想这是一个普遍的问题，当我处理多个工作簿，并不局限于只是复制单元格······

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

Answer 1

你可以做

wkb2.worksheets（“raw”）.range（单元格（1,1).address&“：”&单元格(last_row,5).address).copy

和

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