How to copy specific worksheets into a new workbook but with values only?

[phyxius117]

Hello all,

I have figured most of it out myself and after looking around on the internet I still can't figure out a solution to make it value only when saving to a new workbook. I have tested the current code and it works but the new workbooks still have my original formulas in them which is mostly referencing another sheet in the active workbook. I want this new workbook saved as value only.

Here is the current code:

Sub Export()

Dim DCname As String

DCname = ActiveWorkbook.Sheets("Reference").Range("R2").Value

Dim FiscalYear As String

FiscalYear = ActiveWorkbook.Sheets("Reference").Range("R8").Value

Dim Period As String

Period = ActiveWorkbook.Sheets("Reference").Range("R11").Value

Dim FileName As String

FileName = DCname & "_SQL Data_FY" & FiscalYear & "_P" & Period

Worksheets(Array("Route Totals", "Stops")).Copy

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

Thanks for any help I could get!

 

[rlv01]

One way is to change this

    Worksheets(Array("Route Totals", "Stops")).Copy

    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

to this

    Worksheets(Array("Route Totals", "Stops")).Copy
    
    With ActiveWorkbook.Worksheets("Route Totals")
    .UsedRange.Value = .UsedRange.Value
    End With
    
    With ActiveWorkbook.Worksheets("Stops")
    .UsedRange.Value = .UsedRange.Value
    End With
    
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[phyxius117]

Thank you!!

Sorry I am new to this forum. Will do for future posts!