Selected Answer
Hasson
One problem in your code is that the bold bit
.SaveAs .Path & "\BRANDS" & Format(Date, "DD/MM/YYYY"), 51
produces a string like "..BRANDS25/07/2022.xlsx" for the filename but "/" is an illegal character in Windows filenames.
In the attached file (with 2 worksheets), I've used this modified line to avoid that:
.SaveAs .Path & "\BRANDS " & Format(Date, "DD-MM-YYYY"), 51
The full code below will save a given sheet to a new file. Note the revised With statement means I need to use .Parent.Path to save the new file to your existing workbook's location. I've made some changes (in bold) and added a couple of comments to help:
Sub saveSheet()
Dim ws As Worksheet, NewWb As Workbook
Set ws = ThisWorkbook.Sheets("sheet2")
'create new file
Set NewWb = Workbooks.Add
Application.ScreenUpdating = False
With ws
'copy desired sheet to new file
.Copy Before:=NewWb.Sheets(1)
' save and close new file
Application.DisplayAlerts = False
NewWb.SaveAs .Parent.Path & "\BRANDS " & Format(Date, "DD-MM-YYYY"), 51
NewWb.Close
' restore then close this file
Application.DisplayAlerts = True
Application.ScreenUpdating = True
.Parent.Close
End With
End Sub
You might decide to move .Parent.Close before the line Application.DisplayAlerts = True (if you want to close the first file without saving or the option to do so).
Your line Application.Close will not run (since you closed the file / macro before). You could move it to the Workbook_BeforeClose event macro.
Hope this fixes your problem.
