Export a copy of Workbook without Macro/Formulas

[jpsimmon]

I have been using the below macro to export worksheets without macros/formulas, now I am looking to export the whole workbook without formulas and macros. Export it as a "flattened" workbook.

Sub Flatten()
Dim wbDest As WorkbookDim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
Dim r As Long, c As Long, ws As Worksheet
On Error GoTo ErrorHandler


Application.ScreenUpdating = False


strSavePath = "S:\Location\"


Set wbSource = ActiveWorkbook


For Each sht In wbSource.Sheets(Array("Summary", "Res", "Det", "Apt", "Row", "Semi", "HPI Summary", "Attached", "Graph Data"))
r = sht.Rows.Find("*", , , , xlByRows, xlPrevious).Row
c = sht.Columns.Find("*", , , , xlByColumns, xlPrevious).Column
sht.Copy
Set ws = ActiveSheet
ws.Range("A1").Resize(r, c).Value = sht.Range("A1").Resize(r, c).Value
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
Next


Application.ScreenUpdating = True


ErrorHandler:


End Sub

 

[mole999]

if you copy, paste special values from your code for the areas you want to remove formulas from, then save as xlsx, which is macro free