Copy specific row and paste in next open row

[JCHuysamer]

Hi All
I have found a lot of VBA code but none seems to do the job.

I have a Excel spreadsheet where the template is locked (Sheet protected) but I do need the users ta add additional lines if required (copy Row A3 (hidden) and paste it on the next available unused row - by clicking the command button).

I have the copy part down but i cant get it to paste in the next available line - the only column that will always have data is "H"

 

[Fishboy]

Hi All
I have found a lot of VBA code but none seems to do the job.

I have a Excel spreadsheet where the template is locked (Sheet protected) but I do need the users ta add additional lines if required (copy Row A3 (hidden) and paste it on the next available unused row - by clicking the command button).

I have the copy part down but i cant get it to paste in the next available line - the only column that will always have data is "H"

Hi JCHuysamer, welcome to the boards.

It sounds like you need to define the last row in your code. There are several ways you can do this, but the one I use the most often is as follows:

' Defines LastRow as the last row in column H containing data, then adds 1 more row so you can paste to the next "free" row
LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Rows +1

Now armed with this you can try something like this in a COPY of your workbook. You may want to change the bold red TEST to match the password for your workbook:

Sub CopyHiddenRow()
' Unprotects active sheet with password
ActiveSheet.Unprotect "TEST"
' Disables screen updating to reduce screen flicker
Application.ScreenUpdating = False
' Defines LastRow as the last row in column H containing data, then adds 1 more row so you can paste to the next "free" row
LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row + 1
' Unhides row A3
Range("A3").EntireRow.Hidden = False
' Code to copy row A3 and paste to the next free row
Range("A3").EntireRow.Copy Destination:=Range("A" & LastRow)
' Rehides row A3
Range("A3").EntireRow.Hidden = True
' Re-enables screen updating
Application.ScreenUpdating = True
' Reprotects active sheet with password
ActiveSheet.Protect "TEST"
End Sub

 

[JCHuysamer]

You my friend are a true GENIUS - It WORKS PERFECTLY - now if I could only get the command button to float down with the screen as I add lines

 

[Fishboy]

You my friend are a true GENIUS - It WORKS PERFECTLY - now if I could only get the command button to float down with the screen as I add lines

Happy to help mate