Forum Discussion
problem copying and pasting
OK, I see what's happening.
The cells in columns A and B on the Members1 sheet have font colors applied manually, but those are overruled by the conditional formatting rules.
For example, the text ANGELLE GARY in A4 and B4 is formatted manually as red, but since M4 contains Senior, the conditional formatting rule overrules that and colors the text blue.
When the code copies columns A and B to columns B and C on the Score Input-Flights sheet, both the manual formatting and the conditional formatting are copied.
But none of he conditional formatting rules is applied, since the rules now refer to column M on the Score Input-Flights sheet, and that column is empty.
So the cells display the manually applied colors. On the second sheet, ANGELLE GARY is red, not blue.
To keep the conditional formatting rules working, you have to copy column M too:
Sub Test() Application.ScreenUpdating = False Sheets("Members1").Range("A1:B200").Copy With Sheets("Score Input-Flights").Range("B1:C200") .PasteSpecial Paste:=xlFormats .PasteSpecial Paste:=xlValues End With Sheets("Members1").Range("M1:M200").Copy With Sheets("Score Input-Flights").Range("M1:M200") .PasteSpecial Paste:=xlFormats .PasteSpecial Paste:=xlValues End With Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
If I copy names from the first sheet to the second sheet manually, the colors are preserved.
You didn't include the macro in the workbook.
Could you post the code?
No, I don't use that.
- Have you used the Microsoft Quick Assist App? I would like for us to link up so I can show you what's going on.
It works for me in your sample workbook. In what way does it fail for you? Please provide detailed information: "still not working correctly" doesn't tell me much.
- Hans, I know you are really trying to help and I can't tell you how much I appreciate it.
- Tried it but still not working correctly
Try this version:
Sub Test() Dim fc As FormatCondition Dim f As String Application.ScreenUpdating = False Sheets("Members1").Range("A1:B200").Copy With Sheets("Score Input-Flights").Range("B1:C200") .PasteSpecial Paste:=xlFormats .PasteSpecial Paste:=xlValues For Each fc In .FormatConditions f = fc.Formula1 fc.Modify Type:=xlExpression, Formula1:=Replace(f, "(", "(Members1!") Next fc End With Application.CutCopyMode = False Application.ScreenUpdating = True End Sub- Problem is Column M is referenced in several places so I can't move it.
I tried to reference the conditional formula on Members1 for Score Input-Flights but can't make it work. You could move column M so that there is room for the code to copy the data.
- This won't work because Score Input-Flights Col M is already populated (in use).
OK, I see what's happening.
The cells in columns A and B on the Members1 sheet have font colors applied manually, but those are overruled by the conditional formatting rules.
For example, the text ANGELLE GARY in A4 and B4 is formatted manually as red, but since M4 contains Senior, the conditional formatting rule overrules that and colors the text blue.
When the code copies columns A and B to columns B and C on the Score Input-Flights sheet, both the manual formatting and the conditional formatting are copied.
But none of he conditional formatting rules is applied, since the rules now refer to column M on the Score Input-Flights sheet, and that column is empty.
So the cells display the manually applied colors. On the second sheet, ANGELLE GARY is red, not blue.
To keep the conditional formatting rules working, you have to copy column M too:
Sub Test() Application.ScreenUpdating = False Sheets("Members1").Range("A1:B200").Copy With Sheets("Score Input-Flights").Range("B1:C200") .PasteSpecial Paste:=xlFormats .PasteSpecial Paste:=xlValues End With Sheets("Members1").Range("M1:M200").Copy With Sheets("Score Input-Flights").Range("M1:M200") .PasteSpecial Paste:=xlFormats .PasteSpecial Paste:=xlValues End With Application.CutCopyMode = False Application.ScreenUpdating = True End Sub