Copy / paste actual interior color

[spacely]

Hi,

I need to copy the conditionally formatted cells and paste format to a new range. Currently I do:

Workbooks(EnvelopeFile).Worksheets("System Envelopes").Cells.Copy
Sheets(xlSheet.name).Cells.PasteSpecial Paste:=xlPasteValues
Sheets(xlSheet.name).Cells.PasteSpecial Paste:=xlPasteFormats

Which doesn't take conditional formats. I discovered how to grab just the true interior color, even when set from conditions:

Worksheets("System Envelopes").Range("O109").DisplayFormat.Interior.Color

...but I don't know how to generalize a copy/paste of a whole sheet to another sheet, as I do above, but with all the conditionally set formats. The most important formats really are interior color, font color, and bold. That would be good enough.

Could you help here?

Dave

 

[My Aswer Is This]

Why not just copy the active sheet like this:
This script will copy the active sheet and make a new sheet for you.
Just enter the new sheet name in the Input Box

Sub Copy_Sheet()
'Modified 6/26/2019 11:52:24 PM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim ans As String
ans = InputBox("Enter New sheet name")
If Len(ans) = 0 Then MsgBox "You entered nothing or pressed Cancel" & vbNewLine & "I will Stop script": Exit Sub
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = ans
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "You may have enetered a Improper sheet name." & vbNewLine & _
"Or this sheet name already exist" & vbNewLine & "I will delete the sheet I just added"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[spacely]

Will it just copy values, with no equations? The sheet is a part of a huge connected excel which relies on things from other sheets that might not work in a new book....

 

[My Aswer Is This]

The script makes a exact copy of the sheet.

Will it just copy values, with no equations? The sheet is a part of a huge connected excel which relies on things from other sheets that might not work in a new book....

 

[spacely]

What I am doing is copying the sheet to a new book with a new sheet that is already created by me elsewhere. I'm just trying to copy values and formats over to that new book/sheet... to reduce the size of the whole process.

 

[spacely]

I made this solution. It works.

LastRow = Worksheets("System Envelopes").Cells.SpecialCells(xlCellTypeLastCell).Row
LastColumn = Worksheets("System Envelopes").UsedRange.Columns(Worksheets("System Envelopes").UsedRange.Columns.Count).Column
' this loop is to catch conditional formats
For i = 1 To LastRow
For j = 1 To LastColumn
bclr = Workbooks(EnvelopeFile).Worksheets("System Envelopes").Cells(i, j).DisplayFormat.Interior.Color
bB = bclr \ 65536
bG = (bclr - bB * 65536) \ 256
bR = bclr - bB * 65536 - bG * 256
Sheets(xlSheet.name).Cells(i, j).Interior.Color = RGB(bR, bG, bB)
fclr = Workbooks(EnvelopeFile).Worksheets("System Envelopes").Cells(i, j).DisplayFormat.Font.Color
fB = fclr \ 65536
fG = (fclr - fB * 65536) \ 256
fR = fclr - fB * 65536 - fG * 256
Sheets(xlSheet.name).Cells(i, j).Font.Color = RGB(fR, fG, fB)
Next j
Next i

 

[My Aswer Is This]

Glad to see you know Vba well enough to write your own script which works for you.
It's always nice to see users know Vba and can sort things out for themselves sometimes.
I do not understand all this but glad it works for you.

Take care and if you need more help let us know.

 

[spacely]

Well, I googled around

Thanks for giving hints.