Hey guys. I am trying to use VBA to export all worksheets of a file into their own excel file which I plan to send out on a monthly basis. I am wanting to do a SaveAs to overwrite the existing files without the prompt when I save them to the same folder each month. I will not be changing the file location or wanting the file name changed, just the file overwritten. It's essentially mixing the two youtube videos that I found online called "VBA SaveAs to Overwrite Existing File without Prompt" and "Fast Export All Worksheets into Their Own Excel File". The code does not seem to be working when I use the Application.DsiplayAlerts code in combination with saving multiple worksheets into their own file. That is I don't know how to make it work and I'm assuming I need to make a little change in the code to accomadate this combination. Can someone please help me with specifics in what to change to make this work? To be clear I got both of these coding tasks to work individually, it is just the combination of tasks is what I'm needing. I get an error 1004 Method 'SaveAs' of object'_workbook' failed error. Thanks for any help!
Sub WorksheetExport()
Dim ws As Worksheet
Dim wsDash As Worksheet
Dim wbToSave As Workbook
Dim filepathToSave As String
Set wsDash = Worksheets("Dashboard")
filepathToSave = "RandomFileNameJustForThisScreenShot\"
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> wsDash.Name Then
Application.DisplayAlerts = False
ws.Copy
Set wbToSave = ActiveWorkbook
wbToSave.SaveAs _
Filename:=filepathToSave & wbToSave.Worksheets(1).Name & ".xlsx", _
FileFormat:=51
Application.DisplayAlerts = True
wbToSave.Close True
End If
Next ws
End Sub