Macro that will find the next empty row in a table

[Anon415]

Hi...I am currently working on a budgeting project in MS Excel 2010 and need to copy data from one worksheet to the first available row on the main data worksheet. All of the data on the main worksheet is listed in a table. I have found a code that will take me to the first empty row, however the row it is locating is the first empty row after my table. I need the code to find either the first empty row in the table or the last populated row in the table. Unfortunately there are various blank cells throughout the table. This is what I currently have.


<code>Sub PostPrepaid()
' PostPrepaid Macro
' Post monthly payment amount of all active prepaid accounts to main Data worksheet
Sheets("Prepaid Data").Select
Range("A31:I42").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("DATA").Select Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste
ActiveCell.Offset(9, 0).
Range("A1").Select
End Sub</code>

 

[texasalynn]

Welcome to MrExcel board...

put in a variable like this & replace the code after the copy

LR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Activate
Range("A" & LR).Select
ActiveSheet.Paste

 

[Anon415]

Unfortunately I am still getting the same results...the entries are posting to the first available row after the table...I am new to all of this, I hope I followed your directions correctly..this is the code I used

Sub PostPrepaid()
'
' PostPrepaid Macro
' Post monthly payment amount of all active prepaid accounts to data sheet
'

'
Sheets("Prepaid Data").Select
Range("A31:I42").Select
Selection.Copy
LR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Activate
Range("A" & LR).Select
ActiveSheet.Paste
End Sub

 

[texasalynn]

can you post a sample of your data
the MrExcel HTML makes it easy to see your samples and allows others to copy and then post related answers
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970