VBA command button to insert row at bottom of table using active.cell

[charliew]

Good evening,

I have a table that sometimes i might need to add a row to. I need this row to insert at the bottom of the active cell and still hold the formatting and formulas from the row above it, without the contents of the cell.

I did a record macro as i got fed up trying to figure it out. It works if all i wanted to do was add one row. But i might want to add more than 1 row, but when i click the button again the new row add in the middle of my table and not at the bottom.

Here is my code I'm using.

Sub Add_Lines()'' Add_Lines Macro'
'    Rows("19:19").Select    
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    
Range("A18:T18").Select    
Selection.Copy    
Range("A19:T19").Select    
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False    
Application.CutCopyMode = False    
Range("A18:Q18").Select    
Selection.Copy    
Range("A19:Q19").Select    
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _        
SkipBlanks:=False, Transpose:=False    
Range("B19:D19").Select    
Application.CutCopyMode = False    
Selection.ClearContents
End Sub

ideally, it would be nice if a message box pops up asking how many rows id like to enter...

thanks in advance

 

[DanteAmor]

Sorry, I forgot to translate this part:

Check your formulas, only formats and formulas are being copied.
If you don't want to copy formulas, then remove this line:

Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteFormulas

If you want to copy validationes, try this:

Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteValidation

 

[charliew]

Gracias, jugaré con la fórmula y la teca un poco.

 

[DanteAmor]

De acuerdo, avísame si tienes alguna duda (Ok, let me know if you have any doubt)