Copy data in excel using macro

[Nitya0808]

From Excel X, i want to open Excel Y and copy 1 work. Worksheet name in Excel X is Actuals 2019. The Source Worksheet name in Excel Y is Data 2019. The number of rows will vary everytime.

 

[Masha92]

Hi,

You can use the below code: You didnt mention ranges or anything! So i made it generic and u can tweak it as much as you want!

Sub openz()
Dim sht As Worksheet: Set sht = Sheets("Desintation sheet name")
Dim wb As ThisWorkbook
Application.DisplayAlerts = False
Workbooks.Open Filename:="write the full path of the file including the file name and extension ie .XLSX"
Sheets("Source").Range("A1:A" & Sheets("Source").Cells(Cells.Rows.Count, "A").End(xlUp).Row).Copy
wb.Activate
sht.Range("A1").PasteSpecial xlPasteValues


Workbooks("Filename").Close savechanges = False






Application.DisplayAlerts = True






End Sub

 

[Nitya0808]

Thank you. I am getting an error in wb.Activate line saying variable not set.

Hi,

You can use the below code: You didnt mention ranges or anything! So i made it generic and u can tweak it as much as you want!

Sub openz()
Dim sht As Worksheet: Set sht = Sheets("Desintation sheet name")
Dim wb As ThisWorkbook
Application.DisplayAlerts = False
Workbooks.Open Filename:="write the full path of the file including the file name and extension ie .XLSX"
Sheets("Source").Range("A1:A" & Sheets("Source").Cells(Cells.Rows.Count, "A").End(xlUp).Row).Copy
wb.Activate
sht.Range("A1").PasteSpecial xlPasteValues


Workbooks("Filename").Close savechanges = False






Application.DisplayAlerts = True






End Sub

 

[Masha92]

Apologies,
add this line "Dim wb As Workbook: Set wb = ThisWorkbook" instead of "Dim wb As ThisWorkbook". IT should work now.

Thank you. I am getting an error in wb.Activate line saying variable not set.

 

[Nitya0808]

Thank you so much, the copy works perfectly now.. There is an issue in closing the file

Workbooks("C:\Users\.....\CXYZ.xlsx").Close SaveChanges=False

This line gives a Subscript out of range error