Copying Row Code Doesn't Copy Conditional Formating

[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

 

[Claus Busch]

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.

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.

 

[magmike]

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

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.

 

[Claus Busch]

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.

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


Regards
Claus B.

 

[magmike]

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

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

 

[magmike]

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

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?

 

[Claus Busch]

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

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.

 

[GS]

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

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

 

[magmike]

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

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

 

[Claus Busch]

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."

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.

 

[magmike]

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

That worked. Thanks!