Copy / paste actual interior color

spacely

Board Regular
Joined
Oct 26, 2007
Messages
241
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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


Code:
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

Board Regular
Joined
Oct 26, 2007
Messages
241
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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

Board Regular
Joined
Oct 26, 2007
Messages
241
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

Board Regular
Joined
Oct 26, 2007
Messages
241
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
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.
 

Forum statistics

Threads
1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top