Fetch value from another sheet where the cell reference changes-CodePudding

I wish to grab the value from another sheet where the cell reference will change, it changes because im using the lastrow function to enter a autosum into the last row as the data will always change from day to day.

I'm stuck on how i can reference the cell required from another sheet.

This is the code which creates the auto sum in my sheet named "A" and i want to display the result of this autosum in another sheet.

Dim lastrow1 As Long
lastrow1 = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row   1
ActiveSheet.Cells(lastrow1, "J").Value = "=Sum(J2:J" & lastrow1 - 1 & ")"

CodePudding user response：

You can do this without VBA. I don't know what your data looks like, so let's say this is your data:

My personal preference is to put column sums at the top of the column, that way you can freeze the header and sum rows and have the ability to scroll through the data while still seeing the sums. To do that, put the headers in Row 2 and start the data in Row 3. Then Row 1 would be C1=SUM(C3:C1000). Then you can easily reference C1 in another sheet without VBA.

Alternatively, you could format your data as a table. In the Table Design section of the ribbon, mark "Total Row" to create a row at the bottom that sums up all the data above. With this way, you could reference the cell with the sum by calling VLOOKUP like this: VLOOKUP("Total",Table1[#All],3,FALSE)