Take last data of a table

[imran91]

Hi guys.

I have 500 sheets and i'm using this formula in A11 =index(A1:A10,counta(A1:A10)) to take the last data entry in a table in every sheets. But the problems is that the answer didnt appear because i'm using this formula, =if(B1="","",C1+C2) in A1:A10. So, to edit it back will take a long time. So, what i wonder is that, is there any way to make vba to take the last data and then paste it below the table probably in cell A12 in every sheet? I already attach a simple dummy file. Hope someone can help me..

Thank you

[grumpyguppy]

See below. Cell A11 is the sum of B11:C11.

Private Sub CommandButton1_Click()

Dim DataLastRow, Column As Integer

For Column = 1 To 3
DataLastRow = Worksheets("Sheet1").Cells(Rows.Count, Column).End(xlUp).Row
ActiveSheet.Cells(11, Column).Value = ActiveSheet.Cells(DataLastRow, Column)
Next Column

End Sub

[imran91]

Hi grumpyguppy

Thanks for replying but since i'm rookie in vba, i would like to ask several question.

Do i need to add command button? Where should i paste the code that u gave? In ThisWorkbook or in every sheets?

[grumpyguppy]

Hi Imran,

If you have 500 worksheets (can you possibly have that many?), I reckon you had better list the codes in "This Workbook". In this case you do not need a command button to execute these codes as they will be automatically execute once the workbook in opened. However, in order to work through the 500 sheets, you may need to change the sheets to a variable. See below. I assume you are only interested in the last cell of column A and that column B will also dictate the last cell.

Private Sub Workbook_Open()

Dim DataLastRow As Integer
Dim Sheet As Long

For Sheet = 1 To 3
DataLastRow = Worksheets("Sheet" & Sheet & "").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Sheet" & Sheet & "").Cells(11, 1).Value = Worksheets("Sheet" & Sheet & "").Cells(DataLastRow, 1)
Next Sheet

End Sub