Save Worksheet as new Workbook with Values and Formatting

[Darbyshire]

Hello

I have created the following VBA module, which saves and dynamically names a new workbook which is fine, but I really need to save the active sheet on its own as values and formats only, so it pulls through the data but no formulas but retains all page formatting in the new workbook as well.

Still learning VBA as I go so be glad of any suggestions/help, please. Many Thanks

Sub saveValuesFormat()
'
' saveValuesFormat Macro
'

'
Dim strName As String
Dim companyFilepath As String

strName = Range("AJ1")
companyFilepath = "C:\Users\**************\*************\***************\***************\" & strName

ThisWorkbook.Sheets("ANALYSIS REPORT").Activate
ActiveSheet.Copy
Set tempWB = ActiveWorkbook

With tempWB
.SaveAs Filename:=companyFilepath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
.Close
End With
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

ThisWorkbook.Sheets("ANALYSIS REPORT").Copy
With ActiveWorkbook
   With .Sheets(1).UsedRange
      .Value = .Value
   End With
   .SaveAs Filename:=companyFilepath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
   .Close
End With

 

[Darbyshire]

Thanks that works perfectly, could you also please help me to understand how to make the code differentiate whether we want to save formats or not, as I have similar sheets where I just need to pull out the raw data without the formatting. I currently have a simple macro that saves the file as CSV, but sometimes I need to add additional sheets and formulas to that data at a later date so end up resaving in XLS format.

Be glad of any tips.

Many Thanks

 

[Fluff]

To remove formats you would just add this line

   With .Sheets(1).UsedRange
      .Value = .Value
      .ClearFormats
   End With

 

[Darbyshire]

Thanks, thats great, appreciate your help

 

[Fluff]

You're welcome & thanks for the feedback.