Inser row with the format from the row above.

[pgarcia]

I have the following code but needs some help tricking it.

Private Sub CommandButton1_Click()
ActualRow = Selection.Row
Cells(ActualRow + 1, 1).EntireRow.Insert
Range(Cells(ActualRow, 1), Cells(ActualRow, 3)).Copy _
Destination:=Cells(ActualRow + 1, 1)
End Sub

1. I need to start in cell D34
2. Copy the format in D34 to new row

Thanks

 

[Don Guillett]

This will paste the formats from the last line to a new line below. Adapt to
suit

Sub NewLine()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(lr).Copy
Rows(lr + 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

 

[pgarcia]

Forgot to add, the sheet is password proteced.
Thanks

 

[Don Guillett]

unprotect
code
protect

 

[pgarcia]

Thanks, but that insert a row around 20 (A20?) I need to insert the row after
D34. Thanks

 

[Don Guillett]

what's in
d33
d34
d35

 

[JLGWhiz]

Using your variable:

ActualRow.Offset(1, 0).Row.Insert
ActualRow.Copy ActualRow.Offset(1, 0)

 

[JLGWhiz]

Fix the syntax error.

ActualRow.Offset(1, 0).EntireRow.Insert
ActualRow.Copy ActualRow.Offset(1, 0)

 

[lwm]

Don

I know you wrote this for pgarcia bt I tried it and it alwasy goes to A1.

If I try at A10 it still goes to A1.

How owuld I change this to work at any row.

Thanks

 

[Don Guillett]

IF? you want to copy formats from the active cell row to the next row you
could just modify to

Sub NewLine()
lr = activecell.row 'Cells(Rows.Count, "a").End(xlUp).Row
Rows(lr).Copy
Rows(lr + 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

 

[lwm]

Thanks Don

IF? you want to copy formats from the active cell row to the next row you
could just modify to

Sub NewLine()
lr = activecell.row 'Cells(Rows.Count, "a").End(xlUp).Row
Rows(lr).Copy
Rows(lr + 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)