VBA to create a copy of a worksheet on my desktop but with values rather than formulas

[barnabyr]

Hi, I have written my first VBA macro using the macro recorder. I understand that the recorder adds a lot of unnecessary code but I needed to start learning somewhere!
The macro currently creates an export PDF on my desktop of a specific worksheet, after entering specific values in some cells & applying an advanced filter.
The macro runs through a few different iterations, saving around 30 PDFs on my desktop with different cell values & filters. The PDFs show specific information based on geographical locations.
I've been asked to change it so that rather than creating a PDF it creates a flat excel worksheet with values & formatting rather than data.
So far I have this:

Sub Station_Print()

Dim FilePath As String, dt As String

FilePath = CreateObject("WScript.Shell").specialfolders("Desktop")
dt = Format(CStr(Now), "yyyy-mm-dd")

FIRST ITERATION STARTS HERE

Sheets("As Built Tracker").Select

Range("B2").Select
ActiveCell.FormulaR1C1 = "Station Code"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Station Description"
Range("B3").Select

Range("abt_tracker").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("af_lis"), Unique:=False

Debug.Print FilePath

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FilePath & "\File Name 1 - " & dt & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

FIRST ITERATION ENDS HERE
THEN LOADS OF ITERATIONS GO HERE


Sheets("As Built Tracker").Select

Range("B2").Select
ActiveCell.FormulaR1C1 = "Project Wide"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Project Location"
Range("B3").Select

ActiveSheet.Range("abt_tracker").AutoFilter Field:=2
ActiveSheet.Range("abt_tracker").AutoFilter Field:=3

End Sub

I've added my comments in caps for this post (I imagine there's a way of commenting in the code directly but I haven't got that far yet!)
So if there's a bunch of bloated code I'm obviously happy to cut it out but my main query I'm stumped on, is how to save the worksheet on my desktop & paste the values & formatting in to it.

Sorry if I've rambled on too much!

 

[JLGWhiz]

You can try this. It will create a new workbook by copying the reformatted sheet and then the data will be changed to values only (no formulas) on the new worksheet. Both workbooks remain open.

Sub Station_Print2()
Dim FilePath As String, dt As String
FilePath = CreateObject("WScript.Shell").specialfolders("Desktop")
dt = Format(Date, "yyyy-mm-dd")
    With Sheets("As Built Tracker")
        .Range("B2") = "Station Code"
        .Range("C2") = "Station Description"
        .Range("abt_tracker").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("af_lis")
        .Copy
    End With
    With ActiveWorkbook.Sheets(1).UsedRange
        .Value = .Value
    End With
    With Sheets("As Built Tracker")
        .Range("B2") = "Project Wide"
        .Range("C2") = "Project Location"
        .Range("abt_tracker").AutoFilter Field:=2
        .Range("abt_tracker").AutoFilter Field:=3
    End With
End Sub

 

[barnabyr]

Thanks so much for the speedy reply JLGWhiz!
That is getting much closer to what I'm after but is there a way to save the values worksheet with a different name & not have it open?
I need to create about 30 of these worksheets for the different locations so I would like to just create a copy on the desktop with a specific name & not have it open.

 

[JLGWhiz]

This will allow you to enter a name for your new workbook, and the sheet will be the same name. It will be saved using your FilePath variable as the path.

Sub Station_Print2()
Dim FilePath As String, dt As String
FilePath = CreateObject("WScript.Shell").specialfolders("Desktop")
dt = Format(Date, "yyyy-mm-dd")
    With Sheets("As Built Tracker")
        .Range("B2") = "Station Code"
        .Range("C2") = "Station Description"
        .Range("abt_tracker").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("af_lis")
        .Copy
    End With
    With ActiveWorkbook.Sheets(1).UsedRange
        .Value = .Value
RETRY:
        nm = InputBox("Enter a name for the new workbook.  Do Not include file extension.", "WORKBOOK NAME")
            If nm = "" Then
                MsgBox "You must enter a file name.", vbCritical, "MANDATORY ENTRY"
                GoTo RETRY:
            End If
        ActiveSheet.Name = nm
        ActiveWorkbook.SaveAs FilePath & "\" & nm & ".xlsx"
        ActiveWorkbook.Close False
    End With
    With Sheets("As Built Tracker")
        .Range("B2") = "Project Wide"
        .Range("C2") = "Project Location"
        .Range("abt_tracker").AutoFilter Field:=2
        .Range("abt_tracker").AutoFilter Field:=3
    End With
End Sub

I am sure you realize that I have no idea how you name your files and worksheets or even how your files are created and managed, so if you if the code needs to be modifed to include those type things, they have to be spelled out in the posts to the this thread, unless you can do them yourself.

 

[barnabyr]

JLGWhiz you are the absolute man!
My apologies for not being clear enough.
I can’t really share the file name due to confidentiality issues but in my original code where it says:
Filename:=FilePath & "\File Name 1 - " & dt & ".pdf"
Where it says “\File Name 1 - “ that would be then “\File Name 2 - “ in the next iteration, so the filenames are hard coded in the vba rather than a naming prompt.
I was thinking originally I could just change the export line from a PDF to xls but that didn’t work.
Thanks again for your reply, it’s definitely getting closer to what I had in mind!

 

[JLGWhiz]

JLGWhiz you are the absolute man!
My apologies for not being clear enough.
I can’t really share the file name due to confidentiality issues but in my original code where it says:
Filename:=FilePath & "\File Name 1 - " & dt & ".pdf"
Where it says “\File Name 1 - “ that would be then “\File Name 2 - “ in the next iteration, so the filenames are hard coded in the vba rather than a naming prompt.
I was thinking originally I could just change the export line from a PDF to xls but that didn’t work.
Thanks again for your reply, it’s definitely getting closer to what I had in mind!

You're welcome,
regards, JLG