How to inser a new row by copying a Specific row and paste it at bottom of the row using VBA

[saran985]

I need to copy a specific row (row 10) of worksheet1 and paste it to the bottom of the row each time I run the macro. Row 10 needs to be hidden after copying. Each time when I run a macro to insert a new row, a new row has to be inserted into the bottom row.

Need support. Thanks.

 

[My Aswer Is This]

You said:
and paste it to the bottom of the row
A row has no top and bottom
Do you mean after last row on sheet with data
And if we hide row 10 each time how do we copy row 10 next time

 

[saran985]

Hi, thanks for your reply.
Yes, need to be pasted on the last row of the sheet.

I need to create the file as a template. Hence, need to hide row 10 as I do not want the user to enter any data on row 10. When a user runs the macro to insert a row, row 10 needs to be copied and pasted on the last row of the sheet. Users are allowed to enter data from row 11 to the last row and enter more values by pressing the insert row as needed. Hope this gives better clarification of the requirement.

 

[My Aswer Is This]

You said:
When a user runs the macro to insert a row

Do you want this macro to insert a row?
Insert a row where?
And you keep saying last row
Well, the last row on the sheet is row 1Million 500 thousand
If you mean last row with data what column on the sheet will always have data in it this way, I know What column always has data"
Would column A always have data?
I asked:
Do you mean after last row on sheet with data
You now said:
Yes, need to be pasted on the last row of the sheet.

That means row 1 million 500 thousand

 

[saran985]

When the user clicks Insert row, there has to be a new row copied with row 10 formatting properties and insert below row 10 i.e., Row11. Users can be able to fill required data on row 11. For additional inputs, by clicking insert row every time, there has to be a new row inserted after the last row, where in this case row 11 becomes the last row and keeps going on.

Row 10 needs to be hidden as I don't want the user to edit it.

I have uploaded a sample image of the template.

 

[My Aswer Is This]

I know of no script that can be activated when a user clicks on Insert row
But I will keep watching this thread to see what I can learn.

 

[Peter_SSs]

See if this does what you want. I assume
a) that you would be running this code from your "Insert Row Macro" button
b) row 10 is currently hidden as shown in your post #5 image.

Test with a copy of your workbook.

Sub New_Row()
  Dim lr As Long
  
  lr = Columns("A:G").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  If lr < 10 Then lr = 10
  Rows(10).Copy
  Rows(lr + 1).Insert
  Rows(lr + 1).Hidden = False
  Application.CutCopyMode = False
End Sub

 

[saran985]

Hi, super thanks. It worked perfectly fine. I added row 10 hidden to the code to hide the row after it copies.

Sub New_Row()
Dim lr As Long

lr = Columns("A:G").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lr < 10 Then lr = 10
Rows(10).Copy
Rows(lr + 1).Insert
Rows(lr + 1).Hidden = False
Rows(10).Hidden = True
Application.CutCopyMode = False
End Sub

 

[Peter_SSs]

I added row 10 hidden to the code to hide the row after it copies.

That's fine if you need it but my code was designed to be used when row 10 was already hidden.

I assume

b) row 10 is currently hidden as shown in your post #5 image.

Since it is never to be used, why have it unhidden at all?

 

[My Aswer Is This]

I tried to help here but never did understand the objective.
I tried the script provided here and it hides row 10 but then you cannot enter new data in row 10 if it's hidden so not sure what the objective is. But I'm glad to see you now have an answer that works for you