VBA copy paste formatting problem

bcselect

Board Regular
Joined
May 13, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi bcselect,

your code works for me - even with CF.

VBA Code:
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
 
Upvote 0
Hi bcselect,

your code works for me - even with CF.

VBA Code:
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
 
Upvote 0
I think it has something to do with the destination sheet. I made sure all
conditional formatting formulas were removed but still no luck.
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
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

VBA Code:
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top