Hi I am trying to use this code to save each sheet of Excel to a new workbook. However, it is saving the entire workbook to the new filename
Dim path As String
Dim dt As String
dt = Now()
path = CreateObject("WScript.Shell").specialfolders("Desktop") & "\Calendars " & Replace(Replace(dt, ":", "."), "/", ".")
MkDir path
Call Shell("explorer.exe" & " " & path, vbNormalFocus)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'SetVersions
If ws.name <> "How-To" And ws.name <> "Actg_Prd" Then
ws.SaveAs path & ws.name, xlsx
End If
Next ws
What is the quick fix?
Keeping the worksheet in the existing workbook and creating a new workbook with a copy
Dim path As String
Dim dt As String
dt = Now()
path = CreateObject("WScript.Shell").specialfolders("Desktop") & "\Calendars " & Replace(Replace(dt, ":", "."), "/", ".")
MkDir path
Call Shell("explorer.exe" & " " & path, vbNormalFocus)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'SetVersions
If ws.Name <> "How-To" And ws.Name <> "Actg_Prd" Then
Dim wb As Workbook
Set wb = ws.Application.Workbooks.Add
ws.Copy Before:=wb.Sheets(1)
wb.SaveAs path & ws.Name, Excel.XlFileFormat.xlOpenXMLWorkbook
Set wb = Nothing
End If
Next ws
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With