Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error!
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! : ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error!
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error!
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error!
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error!
Ken,
Ok, thanks for the first bit of code! "Ken Johnson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error in code | Excel Discussion (Misc queries) | |||
Error in Code | Excel Discussion (Misc queries) | |||
Error Code 6 | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |