Exporting sheets to FlatFiles in workbook

I have a code I have been using to export my sheets in Workbook as flatfiles; to just keep the values without the formulas. It is working fine but I am looking to amend the code to only export specific pages in each Workbook. What do I add in my code to specify certain sheets to export?

Sub CreateWorkbooks()
Dim wbDest As Workbook
Dim 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
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

Thanks

Macro loops through all sheets in workbook

For Each sht In wbSource.Sheets

Then you could add an "If" statment checking sheet name and let macro work on the specified sheets.

Alf

Thanks Alf.
Actually is there anyway I can just export my whole workbook without formulas, and keeping the original with the macros/formulas??

You could loop through all he sheets in the workbook and use this command to change all formulas to value

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

just be carefull that you don't save this file "over" your original file

Alf

Will this save it under one workbook? versus individual sheets?
I am hoping to just replicate the workbook with only values without impacting the original workbook.

Will this save it under one workbook? versus individual sheets?

Not sure of what you do mean. The command line will convert all formulas to values but this works only on the active sheet, so after looping through all sheets in a workbook you will have a workbook that contains only values and no formulas.

You can then select to save each sheet as a separate workbook, or save the "whole" workbook.

Alf

Thanks Alf,
What do I put in the code to save the "whole" workbook?

Not sure I read your code right but it seems to me that the code loops through every sheet in your "Master" file and saves each sheet as a separete file with sheet name as fil name.

If this is so perhaps you could just loop through your master file, checking sheet name and delet the one do not wish to keep and transfer formulas to values for the other sheets. Then you save this modified file with a new name and as an xlsx file to get rid of the macro as well. This way you will still keep your original "Master" file intact.

Macro something like this perhaps:

Option Explicit

Sub MakeFlat()
Dim i As Integer

Application.DisplayAlerts = False

For i = 1 To Sheets.Count

Sheets(i).Activate

If ActiveSheet.Name = "This" Or ActiveSheet.Name = "That" Then

    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Else

     Sheets(i).Delete

End If

Next

ActiveWorkbook.SaveAs Filename:="myFile.xlsx", FileFormat:=xlOpenXMLWorkbook

Application.DisplayAlerts = True

End Sub

Alf