CODEWITHSUNDEEP
excelvba
ruedi
ruedi

Reputation: 5545

Copy Range Object from one Workbook to another

I try to copy a range from a workbook (opened with vba-excel) to another (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

with this code:

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")

The line .Range(.Cells(5, 1), .Cells(LastRow, 1)).Copy Destination:=ThisWorkbook.Worksheets("SheetB").Range("A6") is highlighted (yellow) by the debugger with the error that the copy method could not be applyed to the range object. The first copy method (just insered by me to check if the error occurs without the Destination part) runs through. I copied the code to another workbook where I apply the copy-destination copy pattern to only one workbook and it is working. Could anyone tell me, why this is not working? The wbKA workbook opens up fine and I can actually perform all I need (Search, Pasting Values into arrays and so on), just the Copy thing doesnt work.

Upvotes: 1

Views: 2801

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Since you are working from Excel, you do not need to open a new instance. That is creating the copy issues. Try this (Untested)

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

Followup from comments.

You cannot use rng.copy Dest.rng when working with different Excel instances. You will have to first copy it and then paste in the next line. See these examples

This will not work

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

This will work

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

Upvotes: 1

Related Questions