Excel VBA Create New Folder & Copy Sheets To It

Excel VBA Create New Folder & Copy Sheets Of Workbook To It

       📁 A new folder with the same name as the workbook (if folder does not exist) is created at the same place with workbook.

The workbook's sheets as new workbooks are copied into this folder.

🔧 Our codes that we used in macro:
Sub createfolder()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&

Application.ScreenUpdating = False
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

With Application
.ScreenUpdating = False
.DisplayAlerts = False

On Error Resume Next
MkDir MyFilePath
For N = 1 To Sheets.Count
SheetName = ActiveSheet.Name
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Name = SheetName
End With
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xlsx"
.Close SaveChanges:=True
End With
.CutCopyMode = False
End With
Application.ScreenUpdating = True

MsgBox "The Process Was Carried Out Successfully", vbInformation
End Sub

All processes with a button click.

1 comment: