Use Save as to save one work sheet in a new work book

[Petar Willhite]

So I am trying to take the active sheet and save it to its own file when I run the following macro. Not sure why it is not running. I appreciate any help.

Thanks,

Petar

Application.ScreenUpdating = False
ActiveSheet.Range("$A$4:$AG$2001").AutoFilter Field:=4, Criteria1:= _
        "=01-Mechanical Engineer"
      
With Worksheets("PC2 Billed IMPORT")
Projectname = Cells(2, 2).Value
End With


ActiveSheet.SaveCopyAs ("\\C:\Users\petar.willhite\Desktop\DisciplineForecastFiles\" & "FC ME - " & Projectname & ".xlsm"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False



ActiveSheet.Range("$A$4:$AG$2001").AutoFilter Field:=4
Application.ScreenUpdating = True

 

[Petar Willhite]

Never mind I was able to figure it out and get it to work with this code

'Mechanical Engineering File

'Turning off Prompts and Stopping Screen Updating
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Remove Selected Formulas
Columns("A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Going to Remove Extra Sheets
Worksheets("PM Tool").Delete


'Filtering to Specific Discipline
ActiveSheet.Range("$A$4:$AG$2001").AutoFilter Field:=4, Criteria1:= _
        "=01-Mechanical Engineer"
'Getting The Project Name
Projectname = Worksheets("PC2 Billed Import").Cells(2, 2).Value

'Setting the file save path and name
Path = "C:\Users\petar.willhite\Desktop\DisciplineForecastFiles\"
Filename = "FC Mechanical Engineering - " & Projectname

'Saving the new file to the folder with name above
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'Removing Filter
ActiveSheet.Range("$A$4:$AG$2001").AutoFilter Field:=4

'Turning Prompts and Screen Updating Back on
Application.ScreenUpdating = True
Application.DisplayAlerts = False

 

[Hui]

Petar

I don't think Saveas saves the worksheet, but in fact saves the whole workbook as a new file

To save a Worksheet, you Copy it to a new Workbook and then save that workbook as a new file

Sub CopySaveWorksheetAsWorkbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Sheet1").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\MyDir\NewFileName.xlsx"
End Sub