Copying Row Code Doesn't Copy Conditional Formating

  • Thread starter Thread starter magmike
  • Start date Start date
M

magmike

I have a command button when pressed inserts a predesigned rowabove the selection. That row has conditional formatting built in. If I were to copy that row and then Insert the copied row manually, the conditional formatting would carry over. But when pressing my button, everything but the conditional formatting carries over.

Any ideas?

Sub Nsert_TemplateRow()
Const sMsg$ = "This will insert a New Row" _
& " ABOVE your currently selected cell." _
& vbLf & vbLf & " Do you wish to continue?"
Const lStyle& = vbYesNo + vbInformation
Const sTitle$ = "Insert Row"

If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then
Sheets("Sheet1").Range("New_Row").Copy
Cells(ActiveCell.Row, 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End If 'vAns
End Sub

Thanks in advance for your help,
magmike
 
Hi,

Am Tue, 14 Oct 2014 09:12:41 -0700 (PDT) schrieb magmike:
I have a command button when pressed inserts a predesigned rowabove the selection. That row has conditional formatting built in. If I were to copy that row and then Insert the copied row manually, the conditional formatting would carry over. But when pressing my button, everything but the conditional formatting carries over.
Click to expand...

try:
Sub Nsert_TemplateRow()
Const sMsg$ = "This will insert a New Row" _
& " ABOVE your currently selected cell." _
& vbLf & vbLf & " Do you wish to continue?"
Const lStyle& = vbYesNo + vbInformation
Const sTitle$ = "Insert Row"

If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then
Sheets("Sheet1").Rows(ActiveCell.Row).Copy
Cells(ActiveCell.Row, 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End If 'vAns
End Sub


Regards
Claus B.
 
Hi,



Am Tue, 14 Oct 2014 09:12:41 -0700 (PDT) schrieb magmike:






try:

Sub Nsert_TemplateRow()

Const sMsg$ = "This will insert a New Row" _

& " ABOVE your currently selected cell." _

& vbLf & vbLf & " Do you wish to continue?"

Const lStyle& = vbYesNo + vbInformation

Const sTitle$ = "Insert Row"



If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then

Sheets("Sheet1").Rows(ActiveCell.Row).Copy

Cells(ActiveCell.Row, 1).Insert Shift:=xlDown

Application.CutCopyMode = False

End If 'vAns

End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

The row that needs to be copied is on another sheet and named "New_Row". The active cell, however will be the row below where I want "New_Row" inserted.
 
Hi,

Am Tue, 14 Oct 2014 09:50:21 -0700 (PDT) schrieb magmike:
The row that needs to be copied is on another sheet and named "New_Row". The active cell, however will be the row below where I want "New_Row" inserted.
Click to expand...

is "New_Row" the complete row? Then it works for me as expected.


Regards
Claus B.
 
Hi,



Am Tue, 14 Oct 2014 09:50:21 -0700 (PDT) schrieb magmike:






is "New_Row" the complete row? Then it works for me as expected.





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

No. It is A4:AQ4. However, I changed it to the whole row, and it still doesn't carry over the formatting
 
Hi,



Am Tue, 14 Oct 2014 09:50:21 -0700 (PDT) schrieb magmike:






is "New_Row" the complete row? Then it works for me as expected.





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

I have tried it manually two ways:

a. copy New_Row and then right-click and select Insert Copied Cells - DOESN'T WORK
b. insert blank row, copy New_Row then right-click and select Paste WORKS

How can we change the code to emulate B?
 
Hi,

Am Tue, 14 Oct 2014 10:10:12 -0700 (PDT) schrieb magmike:
a. copy New_Row and then right-click and select Insert Copied Cells - DOESN'T WORK
b. insert blank row, copy New_Row then right-click and select Paste WORKS
Click to expand...

then try:
Sub Nsert_TemplateRow()
Const sMsg$ = "This will insert a New Row" _
& " ABOVE your currently selected cell." _
& vbLf & vbLf & " Do you wish to continue?"
Const lStyle& = vbYesNo + vbInformation
Const sTitle$ = "Insert Row"

If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then
Cells(ActiveCell.Row, 1).Insert Shift:=xlDown
Sheets("Sheet1").Range("New_Row").Copy
ActiveCell.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If 'vAns
End Sub


Regards
Claus B.
 
I have a command button when pressed inserts a predesigned rowabove
the selection. That row has conditional formatting built in. If I
were to copy that row and then Insert the copied row manually, the
conditional formatting would carry over. But when pressing my button,
everything but the conditional formatting carries over.

Any ideas?

Sub Nsert_TemplateRow()
Const sMsg$ = "This will insert a New Row" _
& " ABOVE your currently selected cell." _
& vbLf & vbLf & " Do you wish to continue?"
Const lStyle& = vbYesNo + vbInformation
Const sTitle$ = "Insert Row"

If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then
Sheets("Sheet1").Range("New_Row").Copy
Cells(ActiveCell.Row, 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End If 'vAns
End Sub

Thanks in advance for your help,
magmike
Click to expand...

This code is working perfectly for me, CF included!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi,



Am Tue, 14 Oct 2014 10:10:12 -0700 (PDT) schrieb magmike:







then try:

Sub Nsert_TemplateRow()

Const sMsg$ = "This will insert a New Row" _

& " ABOVE your currently selected cell." _

& vbLf & vbLf & " Do you wish to continue?"

Const lStyle& = vbYesNo + vbInformation

Const sTitle$ = "Insert Row"



If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then

Cells(ActiveCell.Row, 1).Insert Shift:=xlDown

Sheets("Sheet1").Range("New_Row").Copy

ActiveCell.PasteSpecial xlPasteAll

Application.CutCopyMode = False

End If 'vAns

End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

Now I get the error that "This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."
 
Hi,

Am Tue, 14 Oct 2014 10:40:08 -0700 (PDT) schrieb magmike:
Now I get the error that "This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."
Click to expand...

if your data is formatted as table then try:

Sub Nsert_TemplateRow()
Const sMsg$ = "This will insert a New Row" _
& " ABOVE your currently selected cell." _
& vbLf & vbLf & " Do you wish to continue?"
Const lStyle& = vbYesNo + vbInformation
Const sTitle$ = "Insert Row"

If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then
Cells(ActiveCell.Row, 1).Insert
Sheets("Sheet1").Range("New_Row").Copy
Cells(ActiveCell.Row, 1).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If 'vAns
End Sub


Regards
Claus B.
 
Hi,



Am Tue, 14 Oct 2014 10:40:08 -0700 (PDT) schrieb magmike:






if your data is formatted as table then try:



Sub Nsert_TemplateRow()

Const sMsg$ = "This will insert a New Row" _

& " ABOVE your currently selected cell." _

& vbLf & vbLf & " Do you wish to continue?"

Const lStyle& = vbYesNo + vbInformation

Const sTitle$ = "Insert Row"



If (MsgBox(sMsg, lStyle, sTitle) = vbYes) Then

Cells(ActiveCell.Row, 1).Insert

Sheets("Sheet1").Range("New_Row").Copy

Cells(ActiveCell.Row, 1).PasteSpecial xlPasteAll

Application.CutCopyMode = False

End If 'vAns

End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

That worked. Thanks!
 
Back
Top