VBA copy paste formatting problem

[bcselect]

My workbook contains several worksheets. I have code that copies and pastes conditionally formatted data between sheets. The data transfers fine but I can't get the
formats to copy over correctly. I have tried different paste commands with no luck. Below is one example. Any suggestions as I have just about pulled all remaining hair out.

Sheets("Members1").Select
Range("A4:B203").Select
Selection.Copy
Sheets("Score Input-Flights").Select
Range("B13:C212").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[HaHoBe]

Hi bcselect,

your code works for me - even with CF.

With Sheets("Score Input-Flights").Range("B13:C212")
  Sheets("Members1").Range("A4:B203").Copy
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False

Ciao,
Holger

 

[bcselect]

Hi bcselect,

your code works for me - even with CF.

With Sheets("Score Input-Flights").Range("B13:C212")
  Sheets("Members1").Range("A4:B203").Copy
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False

Ciao,
Holger

 

[bcselect]

I think it has something to do with the destination sheet. I made sure all
conditional formatting formulas were removed but still no luck.

 

[HaHoBe]

Hi bcselect,

why don't you put in words what you want to achieve? What does The data transfers fine but I can't get the formats to copy over correctly. mean? Sorry, English is not my native tongue.

Maybe this is anywhere near to what you are searching

With Sheets("Score Input-Flights").Range("B13:C212")
  Sheets("Members1").Range("A4:B203").Copy
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
  .FormatConditions.Delete
End With
Application.CutCopyMode = False

The additionakl codleline will delete any Conditional Formatting from the target range.

Holger

 

[bcselect]

Hi bcselect,

why don't you put in words what you want to achieve? What does The data transfers fine but I can't get the formats to copy over correctly. mean? Sorry, English is not my native tongue.

Maybe this is anywhere near to what you are searching

With Sheets("Score Input-Flights").Range("B13:C212")
  Sheets("Members1").Range("A4:B203").Copy
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
  .FormatConditions.Delete
End With
Application.CutCopyMode = False

The additionakl codleline will delete any Conditional Formatting from the target range.

Holger

Thanks but this made no difference. I have 3 colors assigned on Members1 with conditional formatting. This data transfers to another sheet. The data goes fine but the colors
don't transfer with them.

 

[HaHoBe]

Hi bcselect,

so the request is: copy the data from Sheets Members1 to Score Input-Flights, insert as values, get rid of the onditional Formatting but keep the results from the CF.

Let's assume it's only the background colour which is changed by CF. The cells keep the original colour and CF overwrites that. So you would have to loop through the cells you copy, read the information from the Conditions of CF, see if it's fitting, get the colour and apply that to the target cell via hard-coding instead of CF.

I don't know if 365 holds an easier way to do this but with Excel2019 I would look for a different way to achieve the wanted result (maybe create an additional part which takes ayway colouring first, loop thorugh the range and compare and colour accordingly. This goes for Borders, Fonts, Fornt-Attributes like Colour and Bold, Size, Strikethrough as well but would need to include Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets).

Holger

 

[bcselect]

I can copy a section of the formatted columns on Sheet1, paste it to a blank area on that same sheet, and the colors transfer fine.

If you try to paste it to a different sheet in the workbook, the text is black: no color.

If you create a new sheet, independent of the original workbook, and try to paste, the text is black: no color.

If you open Word and paste, colors work fine.

Does anyone have an idea what might be going on?