Set workbook color theme via VBA

[jwb1012]

Hello, I was wondering if its possible to use VBA to change the color theme of a worksheet?

I am using the code below to create a new workbook and copy worksheets into this workbook from another workbook. Unfortunately, the workbook color theme in my initial workbook is set on "Violet" - with some cells colored purple for appearance. When the new workbook is created, it is set to the default - "Office" color theme - and my purple cells have turned to light blue.

Any thoughts on how to correct this?

Sub Export()
    Dim Sh As Worksheet, wb As Workbook, txt As String
    Set wb = Workbooks.Add
        On Error Resume Next
            For Each Sh In ThisWorkbook.Sheets
                If Left(Sh.Name, 9) = "Labor BOE" Then
                    Sh.Move After:=wb.Sheets(wb.Sheets.Count)
                End If
            Next Sh
        On Error GoTo 0
    txt = InputBox("Enter the Version Number or Date", "Export")
    wb.SaveAs ThisWorkbook.Sheets("Home").Range("$F$9").Value & "_" & txt & ".xlsx"
    MsgBox "BOE generation is complete."
End Sub

 

[igold]

How about this line somewhere in your code.

DestinationWorkbook.xlsx").Colors=workbooks("SourceWorkbook.xlsx").Colors

Change the name of the workbooks as needed.

igold

 

[jwb1012]

Hello, thank you for the response - was the initial part of your code cut-off? It seems like an open parenthesis and quotation mark may have been.

 

[igold]

Yes indeedy, so much for some on the ball proofreading...

workbooks("DestinationWorkbook.xlsx").Colors=workbooks("SourceWorkbook.xlsx").Colors

Sorry about the confusion!

igold

 

[jwb1012]

Hmm this doesn't seem to work for me based on how I entered. This model will be a template downloaded by multiple people, so icant necessarily use the names of the workbooks within the code. Here is the code I tried based on your guidance with a few modifications. Any thoughts on how to get this to work?

Sub Export_BOE_X()
    Dim Sh As Worksheet
    Dim BOE_Export As Workbook
    Dim Source_wb As Workbook
    Dim txt As String
    Dim Sheet_1_Name As String

[B]    Set Source_wb = ActiveWorkbook      'since the code will be initialized in the Source_wb, i've used Activeworkbook
    
    Set BOE_Export = Workbooks.Add      'creates new workbook[/B]
    
         BOE_Export.Sheets(1).Name = "Labor BOEs"   'Renames first worksheet
               
        On Error Resume Next
            For Each Sh In ThisWorkbook.Sheets
                If Left(Sh.Name, 9) = "Labor BOE" Then
                    Sh.Move After:=BOE_Export.Sheets(BOE_Export.Sheets.Count)   'moves all "Labor BOE" worksheets to new workbook ("BOE_Export")
                End If
            Next Sh
        On Error GoTo 0

        [B]Source_wb.Colors = BOE_Export.Colors    'Set Source_wb and BOE_Export COLOR THEMES equal to each other[/B]
         
    txt = InputBox("Enter the Version Number or Date", "Export")    'Insert box for document version number or date
    BOE_Export.SaveAs ThisWorkbook.Sheets("Home").Range("$F$9").Value & "_" & txt & ".xlsx"     'Rename BOE_Export
    MsgBox "BOE generation is complete."
End Sub

 

[igold]

Untested, and I have never used it myself in code, but I think you may have use the Workbooks object in the line, perhaps something like this:

Workbooks(Source_wb.Name).Colors = Workbooks("BOE_Export.xlsx").Colors

Also, if BOE_Exports is not saved yet then I am not sure what effect that may have. Again, I am throwing darts here...

igold