neodjandre
Well-known Member
- Joined
- Nov 29, 2006
- Messages
- 950
- Office Version
- 2019
- Platform
- Windows
Hey all,
I am using this code to export values to a new workbook. However, the new workbook automatically uses a different color theme for sheets and cell coloring compared to my original workbook.
How could I retain my original color theme? Any help would be much appreciated.
I am using this code to export values to a new workbook. However, the new workbook automatically uses a different color theme for sheets and cell coloring compared to my original workbook.
VBA Code:
Sub export_values()
Dim sourceWB As Workbook
Dim destWB As Workbook
Dim ws As Worksheet
Dim path As String
Dim fname As String
Dim sh As Worksheet
Dim i As Integer
Dim shp As Shape
Dim e_comp As String
Dim lastRow As Long
Dim er As Long
e_comp = ThisWorkbook.Names("i_co").RefersToRange(1, 1)
path = ThisWorkbook.path & "\"
fname = e_comp & "_values_" & Format(Now, "dd_mmm_yy_hh_mm_ss")
fname = clean_filename(fname, "")
Set sourceWB = ThisWorkbook
Set destWB = Workbooks.Add
destWB.SaveAs path & fname, FileFormat:=xlExcel12
For Each ws In sourceWB.Worksheets
If CLng(ws.Range("B1").value) = "2" Then
i = i + 1
ws.Copy After:=destWB.Sheets(destWB.Sheets.Count)
End If
Application.StatusBar = "Processing..." & ws.Name
Next ws
For Each sh In destWB.Worksheets
sh.Activate
With sh.UsedRange
.value = .value
End With
Application.StatusBar = "Converting..." & sh.Name
Next sh
destWB.Save
End Sub
How could I retain my original color theme? Any help would be much appreciated.