VBA to copy cell value from the last row of the workbook to another workbook

[shernel07]

Hi there,

I have two workbooks eg. Data.xlsx and Final.xlsx

In Data.xslx I have the sum of A2:A3 in cell A4

Now I need a macro code to copy the data from cell A4 and transfer it to the Final.xlsx workbook in cell A4 for example.

P.S I need to data to get transferred not the formula present in A4.

Can you guys help me in this? Thanks

 

[Danmc]

Workbooks("Final.xlsx").Sheets("Sheet1").Range("A4") = Workbooks("Data.xlsx").Sheets("Sheet1").Range("A4")

 

[shernel07]

That cell A4 would always change. So I need a code which always selects the last row with value in column A.

 

[Danmc]

so your saying that you need the last cell in column A then?

 

[shernel07]

In workbook Data.xlsx , I want the cell value of the last row in Column A to be copied to the Final.xlsx workbook.

The last row would always vary

 

[Danmc]

Sub transfer()
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Workbooks("Final.xlsx").Sheets("Sheet1").Range("A4") = Workbooks("Data.xlsx").Sheets("Sheet1").Range("A" & LastRow)
End Sub

is that what you are looking for?

 

[shernel07]

Yes exactly what I was looking for but when I run it why does it show Subscript out of range??? Run time error 9.

I copy pasted the code in the Final.xlsx workbook.

Is there some location of the workbook that needs to be provided?

 

[shernel07]

Or sorry I had to copy the code in the data workbook

 

[shernel07]

Hello just one last favour : I want to add a button on the Final.xlsx sheet which pulls the data of the last row in column A in data.xlsx sheet to the Final.xlsx

The code you have provided sends data from Data.xlsx to the Final.xlsx which I do not want making the code to be present on the Data.xlsx workbook

I want the code to be on the Final.xlsx workbook where when i click the button the data on the last row of Data.xlsx reflects in Final.xlsx

 

[Danmc]

Sub transfer()
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long
Set wb = Workbooks("Data.xlsx")
Set ws = wb.Sheets("Sheet1")
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Workbooks("Final.xlsx").Sheets("Sheet1").Range("A4") = Workbooks("Data.xlsx").Sheets("Sheet1").Range("A" & LastRow)
End Sub

place this in a standard module in Final. Assign macro to button/shape.

note that final.xlsx extension will change to final.xlsm (because you are adding code to the final sheet, so make that change in the code)