problem copying and pasting

[bcselect]

I have a workbook with several worksheets.  On one sheet there are 2 columns of text (Lname & Fname).

All of the cells are conditionally formatted with either of 3 colors.

There is code in a macro that copies and pastes those columns to another sheet.

Problem is that the colors change when pasted.  It's like the 2nd sheet contains formatting that cannot be overwritten.  They need to copy and paste as original.

I tried clearing formats prior to copying but no better.

Called Microsoft support but they found no problem with Excel and suggested this site for support.

[#2]

@bcselect 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

Thanks for responding. I surely hope you can come up with an answer.
How do I attach the file?

[#4]

@bcselect 

If you see an area below the box where you compose a reply that says "Drag and drop here or browse files to attach", drag the workbook there.

If you don't see it, you should be able to attach it to a private message to me (click on my user picture).

Or you can upload it to a cloud service such as Google Drive or OneDrive, then obtain a share link to the uploaded file and post that link in a reply here.

[#5]

Did you receive the file?

[#6]

@bcselect 

No, I don't see an attachment, nor a link, nor a private message from you.

[#7]

I just sent it again. Did you get it?

[#8]

@bcselect 

Yes, I did, thanks. I will take a look.

[#9]

@bcselect 

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?

[#10]

i tried manually and saw the problem. i will send the code.

[#11]

Sheets("Members1").Select
Range("A1:B200").Select
Selection.Copy
Sheets("Score Input-Flights").Select
Range("B1:C200").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

[#12]

Pay particular attention to rows 110 and 117. compare the colors before and after on both sheets.

[#13 · Accepted solution]

@bcselect 

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

[#14]

This won't work because Score Input-Flights Col M is already populated (in use).

[#15]

@bcselect 

You could move column M so that there is room for the code to copy the data.

[#16]

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.

[#17]

@bcselect 

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

[#18]

Tried it but still not working correctly

[#19]

Hans, I know you are really trying to help and I can't tell you how much I appreciate it.

[#20]

@bcselect 

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.