Find 1st blank/empty cell in Columm A and then Copy /Paste that same entire row

[brianv]

So this is a followup to another post i recently posting regarding unprotecting and reprotecting a sheet when entering data based on a userform. Thanks for the assistance on that....

The WS Im working on is our Project Log and has 350+ projects listed on it, each row is a specific project and each of the column are milestone dates for that project.

Ive created UserForm to assist the sales staff to add projects to the list, the UserForm essentially finds the first empty of data, and enters data from the UserForm into specific cells on that same row.

This is the code within the UserForm, the button on the spreadsheet opens the UserForm, and a "submit" button in the UserForm will do the following:

Private Sub CmdSubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Active")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'Range("A65535").End(xlUp).Offset(1, 0).Select

'Unprotect WorkSheet
Sheets("Active").Unprotect "xxxx"

'copy the data to the database
ws.Cells(iRow, 1).Value = "Yes"
ws.Cells(iRow, 2).Value = Me.TextJobNumber.Value
ws.Cells(iRow, 5).Value = Me.ComboPVWage.Value
ws.Cells(iRow, 6).Value = Me.TextProjectName.Value
ws.Cells(iRow, 7).Value = Me.TextCustomer.Value
ws.Cells(iRow, 8).Value = Me.TextSalesman.Value
ws.Cells(iRow, 9).Value = Me.TextSystemType.Value
ws.Cells(iRow, 10).Value = Me.TextPanel.Value
ws.Cells(iRow, 11).Value = Me.ComboProjectType.Value
ws.Cells(iRow, 12).Value = Me.ComboInstallType.Value
ws.Cells(iRow, 13).Value = Date
ws.Cells(iRow, 14).Value = Me.ComboPriority.Value
ws.Cells(iRow, 15).Value = Me.TextValue.Value
ws.Cells(iRow, 21).Value = Me.TextDesignHours.Value
ws.Cells(iRow, 22).Value = Me.ComboDesignOT.Value
ws.Cells(iRow, 25).Value = Me.TextSubmittalDate.Value
ws.Cells(iRow, 26).Value = Me.TextDwgDate.Value
ws.Cells(iRow, 54).Value = Me.TextInstallHours.Value
ws.Cells(iRow, 55).Value = Me.ComboInstallOT.Value


'close theNew Project Entry Form
Unload Me

Range("A65535").End(xlUp).Offset(1, 0).Select

'Reprotect Worksheet
Sheets("Active").Protect "xxxx", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

End Sub

But in addition to adding the UserForm data, i would like to have the same "submit click" to to Copy/Insert Paste that 1st empty row same position, thereby increasing the row count to add the new data into. Column A is what is use to find the last used row and then Im offsetting by one.

So lets say I have rows pre-formatted with formulas and what-not between rows 10:360, and row 355 is my last row of project data, meaning 356:360 is formatted but blank, the code needs to find row 356 (which it already does) and then copy/insert paste that same row back into row 356, thereby increasing my formatted rows by one.

Im sure this is relatively simple, Im just not fully understanding the code needed.

Thanks
Brian

 

[brianv]

Hello,
Im still looking for assistance on this, is anyone able to help....

Thanks
Brian

 

[NoSparks]

Have you looked into the use of Excel tables ?
Formulas and formatting automatically get extended to a new row when you add it.
No need for any pre-formatted rows.

A couple of members have pages for dealing with tables.
https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables