Copy value from last cell in Column

[Podder1965]

Hi,
Im trying to copy the last cell of a column of data in sheet2, add 1 to the value, then paste the new value into sheet1 (same workbook) in cell A1.
So column last value sheet2= 10
Want to add 1 to this = 11
Then paste this value into cell A1 sheet1.

 

[kweaver]

Welcome to Mr. Excel.

Which column has the data in the last row that you want copied?
Is the last value always in the 10th row or could it be any row in the in the column?
Are there any blanks before the last entry?

If the data is in column A in Sheet2, is that what you want as the formula in Sheet1!A1 ?

=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))

If there are possibly blanks in that column, try this array formula (CTRL+Shift+Enter)

=INDEX(Sheet2!A:A,MATCH(2,1/(Sheet2!A:A<>"")))

 

[Podder1965]

kweaver,
Thanks, it is what im looking for but would like it in vba, sorry, i forgot to mention that in the initial post.
Do you know how to do this?

 

[kweaver]

Actually, I didn't add 1 in my formula.

Try:

Sub Add_1()
Dim LR As Long
LR = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet1").Range("A1").FormulaArray = "=1+INDEX(Sheet2!A:A,MATCH(2,1/(Sheet2!A:A<>"""")))"
Sheets("Sheet1").Range("A1") = Sheets("Sheet1").Range("A1").Value
End Sub

 

[kweaver]

You only need this:

Sub Add_1()
Sheets("Sheet1").Range("A1").FormulaArray = "=1+INDEX(Sheet2!A:A,MATCH(2,1/(Sheet2!A:A<>"""")))"
Sheets("Sheet1").Range("A1") = Sheets("Sheet1").Range("A1").Value
End Sub

 

[Podder1965]

Kweaver,
Perfect, much appreciated

 

[Peter_SSs]

.. or you only need this.

Sub Add1()
  Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Value + 1
End Sub

 

[Podder1965]

Thanks, Peter
More than one way to skin a cat

 

[Peter_SSs]

Thanks, Peter
More than one way to skin a cat

Sure.
I just thought it simpler to put the number straight in rather than have a formula calculate it then replace the formula with its own result.
It makes no real difference either way of course.

[Further note: Also, my code would fail if the Sheet2 column contained formulas returning "" at the bottom but I interpreted your information as if it was not that, though another one-liner could do the job in that case]