I have the following code (that I borrowed) and was using it to export some worksheets to a new workbook. It worked fine for about 4 times, now it says it can't find the file path and stops on the "MkDir FolderName" line.
Sub ExportWorkbook()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
For Each sh In ThisWorkbook.Worksheets
With sh
If LCase(Right(.Name, 2)) = "sd" Then
.Visible = xlSheetVisible
End If
End With
Next sh
Sheets("Order").Visible = xlSheetHidden
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook
'Create new folder to save the new files in
DateString = Format(Now, "mm-dd-yyyy hh:mm")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName
'Copy every visible sheet to a new workbook
For Each sh In Sourcewb.Worksheets
'If the sheet is visible then copy it to a new workbook
If sh.Visible = -1 Then
sh.Copy
'and so on
The sourcewb is in a folder on my desktop. When this worked correctly, it created a new folder within that same folder. (I haven't moved anything.) That would be great but I'm over my head here... Any help is greatly appreciated. Thank you!
Bookmarks