复制Range对象从一个工作簿到另一个

Question

我试着从一个工作簿（使用VBA-Excel中打开），使用此代码复制至另一（的ThisWorkbook）

Public wbKA as workbook 
Sub A() 
Dim oExcel As Excel.Application 
KAPath = ThisWorkbook.path & "\Data.xlsx" 
Set oExcel = New Excel.Application 
Set wbKA = oExcel.Workbooks.Open(KAPath) 
... 
End Sub 

：

Sub Get() 
Dim LastRow As Long 
    With wbKA.Worksheets("Sheet1") 
     LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
     .Range(.Cells(5, 1), .Cells(LastRow, 1)).Copy 
     .Range(.Cells(5, 1), .Cells(LastRow, 1)).Copy Destination:=ThisWorkbook.Worksheets("SheetB").Range("A6") 

线路.Range(.Cells(5, 1), .Cells(LastRow, 1)).Copy Destination:=ThisWorkbook.Worksheets("SheetB").Range("A6")被调试器高亮显示（黄色），错误代码为copy method could not be applyed to the range object。第一种复制方法（我只是为了检查错误是否发生在没有目标部分的情况下才被执行）。我将代码复制到另一个工作簿，我将复制目标复制模式应用于仅一个工作簿，并且它正在工作。谁能告诉我，为什么这不起作用？ wbKA工作簿打开正常，我可以实际执行所有我需要的（搜索，粘贴到数组等等），只是复制的东西不起作用。

Answer 1

由于您使用的是Excel，因此您无需打开新的实例。这是创建复制问题。尝试从本评论（未经测试）

Sub Sample() 
    Dim thisWb As Workbook, thatWb As Workbook 
    Dim thisWs As Worksheet, thatWs As Worksheet 
    Dim KAPath As String 
    Dim LastRow As Long 

    Set thisWb = ThisWorkbook 
    Set thisWs = thisWb.Sheets("SheetB") 

    KAPath = ThisWorkbook.Path & "\Data.xlsx" 

    Set thatWb = Workbooks.Open(KAPath) 
    Set thatWs = thatWb.Sheets("Sheet1") 

    With thatWs 
     LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 

     .Range(.Cells(5, 1), .Cells(LastRow, 1)).Copy thisWs.Range("A6") 
    End With 
End Sub 

跟进。

使用不同的Excel实例时，不能使用rng.copy Dest.rng。您必须先复制它，然后粘贴到下一行。看到这些例子

这将无法正常工作

Sub Sample() 
    Dim xl As New Excel.Application 
    Dim wb As Workbook 
    Dim ws As Worksheet 

    xl.Visible = True 
    Set wb = xl.Workbooks.Add 
    Set ws = wb.Sheets(1) 

    ws.Range("A1").Value = "Sid" 

    ws.Range("A1").Copy ThisWorkbook.Sheets(1).Range("A1") 
End Sub 

这将工作

Sub Sample() 
    Dim xl As New Excel.Application 
    Dim wb As Workbook 
    Dim ws As Worksheet 

    xl.Visible = True 
    Set wb = xl.Workbooks.Add 
    Set ws = wb.Sheets(1) 

    ws.Range("A1").Value = "Sid" 

    ws.Range("A1").Copy 
    ThisWorkbook.Sheets(1).Range("A1").PasteSpecial xlValues 
End Sub