CODEWITHSUNDEEP
vbaexcelcopy
kris
kris

Reputation: 85

Copy data between workbooks

I have code that copies data from Sheet1 in Workbook1 to Sheet2 in Workbook2:

Sub Copydata()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("C:\Users\User1\Documents\Workbook1.xlsx")
Set y = Workbooks.Open("C:\Users\User1\Documents\Workbook2.xlsx")
With x.Sheets("Sheet1").UsedRange
    'Now, paste to y worksheet:
    y.Sheets("Sheet2").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With
x.Close
y.Close SaveChanges:=True
End Sub

This code works fine and copies the data only if workbook2 is closed. If I run this code from workbook2 by opening the file, the results won't be copied to this workbook.

Can't we copy results to the opened workbook?

Upvotes: 0

Views: 129

Answers (2)

kris
kris

Reputation: 85

Actually setting the workbook2 as active workbook helped. 

 Set y = Application.ActiveWorkbook

Upvotes: 0

Bas Verlaat
Bas Verlaat

Reputation: 852

You can do so if the workbooks are opened in the same application shell. instead of

set x = workbooks.open("C:\Users\User1\Documents\Workbook1.xlsx")

use

set x = workbooks("Workbook1.xlsx")

Upvotes: 0

Related Questions