Finding and selecting first empty cell in a colunm B each time

Trikeman · Mar 5, 2018

Hi
Please would someone help me, I want to copy information for one sheet and paste it to another sheet, the data is going to be in column B, but I need away to make the macro go down to the next empty cell. So if the first time I run the macro I have data down to B4, it goes to B5, the next time B6 etc, believe it or not I have been one this problem for two days now, so I really could do with some help. Below is some code I have copied partly from MR Excel, and it gets me the number of the last row wat data but I can’t get it to either select the last row or better still the first empty cell.

Thank you

[Sub endxlupTest01()
' endxlupTest01 Macro
Sheets("FORMAT DATA SHEET READY FOR COP").Select
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row

MsgBox "The last row today is " & FinalRow
FinalRow.Select

End Sub]
Thank you again for reading and any help you can give me

Fluff · Mar 5, 2018

How about

Code:

 Cells(Rows.Count, 2).End(xlUp).Offset(1).Select

My Aswer Is This · Mar 5, 2018

Here is how your current script should look.

Code:

Sub endxlupTest01()
' endxlupTest01 Macro
Sheets("FORMAT DATA SHEET READY FOR COP").Select
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
MsgBox "The last row today is " & FinalRow
Cells(FinalRow, 2).Select
End Sub

My Aswer Is This · Mar 5, 2018

If your wanting to copy values from one sheet to another you do not need to use select and activate.

You can us a script like this:

Read the script and see the sheet names.

Code:

Sub endxlupNew()
' endxlupTest01 Macro
'Modified 3-5-18 9:00 AM EST
Dim FinalRow As Long
FinalRow = Sheets("FORMAT DATA SHEET READY FOR COP").Cells(Rows.Count, 2).End(xlUp).Row + 1
Sheets("Apple").Range("A1").Copy Sheets("FORMAT DATA SHEET READY FOR COP").Cells(FinalRow, 2)
End Sub

Trikeman · Mar 5, 2018

Thank you will give it a try and report back

Trikeman · Mar 5, 2018

Thank you I have tried this one and is working for me, the one below was a short macro but when I copy I needed to do a paste special and I was able to add that in with this one.

My Aswer Is This · Mar 5, 2018

Trikeman said:
Thank you I have tried this one and is working for me, the one below was a short macro but when I copy I needed to do a paste special and I was able to add that in with this one.

Sounds like you have what you need.
If you need more help let us know

Show me your script as you now have it.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Finding and selecting first empty cell in a colunm B each time

Trikeman

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Trikeman

New Member

Trikeman

New Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Finding and selecting first empty cell in a colunm B each time

Trikeman

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Trikeman

New Member

Trikeman

New Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock