Code error!

[Guest]

Hi All,

I have the following code for a button to add a new row below the last line
of information entered. However, the new line that is ceated copies the data
from the line it is copying. i want to copy the formatting but not the actual
information, i would like the new row to be blank. how would i do this?

Private Sub CommandButton1_Click()
'Insert Entire Row
With Sheet1
..Unprotect
With .Range("code")
.End(xlDown).Offset(1, 0).EntireRow.Insert
.Offset(1, 0).Resize(1, 20).Copy
Destination:=.End(xlDown).Offset(1, 0)
Application.CutCopyMode = False
.End(xlDown).Offset(1, 0).Select
End With
..Protect
End With
End Sub

Thanks in advance!

: )

 

[Ken Johnson]

Hi RemySS,
Try this

Private Sub CommandButton1_Click()
'Insert Entire Row
With Sheet1
..Unprotect
With .Range("code")
.End(xlDown).Offset(1, 0).EntireRow.Insert

With .Offset(1, 0)
.Resize(1, 20).Copy
.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
.End(xlDown).Offset(1, 0).Select
End With
..Protect
End With
End Sub

Ken Johnson

 

[Guest]

Hi Ken,

this worked, but also deleted the formatting from the row it copied. the
second column is 5 merged cells - they went to unmerged in both the top row
and the inserted new row. How can i keep that the same?

Thanks!

 

[Ken Johnson]

Hi Remy,
I see that any merged cells become unmerged, and the same happens with
Center Across Selection. Its nearly 3:00 am here and I must get some
sleep. Maybe you could start a new topic about loss of cell merging and
hopefully one of the MVPs can sort it out. Cell merging is the bane of
every Excel user's existance and the MVPs usually poo poo its use, but
here is a case of Center Across Selection formatting also being lost so
you need an MVP to sort it out. I thought all I did was change it from
copy/paste to copy then paste special/ paste formats. The With/End
Withs are a bit convoluted, so I may have overlooked something because
of that.
Sorry I can't help anymore, must get to bed zzzzzzzz

Ken Johnson

 

[Guest]

Ken,

Ok, thanks for the first bit of code!