CODEWITHSUNDEEP
vbaexcel
Bluesector
Bluesector

Reputation: 329

Copy Data from one open Workbook into another open workbook

Following code:

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook

For Each wB In Application.Workbooks
If Left(wB.Name, 21) = "Open Order Monitoring" Then
    Set Wb1 = wB
    Exit For
End If
Next

    Set wb2 = ThisWorkbook

Wb1.Sheets(1).Range("A2").Range(.Cells(1, 1), .End(xlDown).Cells(1, 39)).Copy wb2.Sheets(2).Range("B5")

End Sub

The macro should copy data from a open workbook with variable name (open order monitoring[...]) and paste into the second sheet of the workbook I run the macro from.

But the line:

Wb1.Sheets(1).Range("A2").Range(.Cells(1, 1), .End(xlDown).Cells(1, 39)).Copy wb2.Sheets(2).Range("B5")

gives me an error. can someone solve this problem?

Upvotes: 0

Views: 9952

Answers (2)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Pls try with below code

Sub CopyData()
    Dim Wb1 As Workbook, wb2 As Workbook, wb As Workbook
    Set wb2 = ThisWorkbook
    For Each wb In Workbooks
    If Left(wb.Name, 21) = "Open Order Monitoring" Then
        Set Wb1 = wb
        Exit For
    End If
    Next
    Wb1.Sheets(1).Range("A2:AM2").Copy wb2.Sheets(2).Range("B5") 'Edited here
End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

since:

  • it's always safer to use fully qualified range references (down to workbook and worksheet ones). especially when you're dealing with multiple workbooks and/or worksheets

  • should you only be interested in pasting values, it's faster (and safer, too) use Range1.value = Range2.Value instead of .Copy() method of Range object.

then, here follows a possible code:

Option Explicit

Sub CopyData()
    Dim Wb1 As Workbook, wb2 As Workbook, wB As Workbook
    Dim rngToCopy As Range

    For Each wB In Application.Workbooks
        If Left(wB.Name, 21) = "Open Order Monitoring" Then
            Set Wb1 = wB
            Exit For
        End If
    Next

    If Not Wb1 Is Nothing Then '<~~ check if you actually found the needed workbook
        Set wb2 = ThisWorkbook

        With Wb1.Sheets(1)
            Set rngToCopy = .Range("A2:AM2", .Cells(.Rows.Count, "A").End(xlUp))
        End With
        wb2.Sheets(2).Range("B5:AN5").Resize(rngToCopy.Rows.Count).Value = rngToCopy.Value
    End If
End Sub

Upvotes: 4

Related Questions