Copy value from last cell in Column

Podder1965

New Member
Joined
Feb 10, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,738
Office Version
  1. 365
  2. 2010
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 ?

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

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

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

Podder1965

New Member
Joined
Feb 10, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 8, 2018
Messages
1,738
Office Version
  1. 365
  2. 2010
Actually, I didn't add 1 in my formula.

Try:

Code:
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
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,738
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

You only need this:

Code:
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
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,493
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

.. or you only need this.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,493
Office Version
  1. 365
Platform
  1. Windows
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. (y)

[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,129,538
Messages
5,636,896
Members
416,948
Latest member
Jkpang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top