Hello,
I have a current MACRO I use when ran prompts the user to select a location to save all folders. Once the user selects a location and hits "Ok" the MACRO creates a new folder using a list in excel (E26:E75) that the user can modify so they don't have to manually create folders and rename them.
I need the MACRO to also create and save an excel work book in each folder with the same name as the newly created folder. On a side note, in the same workbook that the users enter the list of folders they want created there is another worksheet/tab that has the a templated worksheet that needs to be saved as a workbook in each newly created folder. Currently, I am asking for help putting an empty workbook in each folder, once this is done each user can go in to each folder and newly created workbook and copy/past the templated worksheet and update as necessary.
Big picture, creating a folder for each "step" with an applicable "workpaper" in each folder. Each "workpaper" should have the same basic template format and stock information but some cells/information will change based on "steps" for each "workpaper".
Any help or guidance would be appreciated, thanks.
Current MACRO:
CODE#
Sub Create_Folders()
'Macro to create folders from list in excel
Dim R As Range
Dim RootFolder As String
RootFolder = GetFolder() 'Uses GetFolder module to prompt user to select location for folders
For Each R In Range("E26:E75") 'Targeted Cells from which to create folders based on
If Len(R.Text) > 0 Then
On Error Resume Next
MkDir RootFolder & "" & R.Text
On Error GoTo 0
End If
Next R
End Sub
Function GetFolder() As String
'Macro to prompt users to select location for files or folders
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
I have a current MACRO I use when ran prompts the user to select a location to save all folders. Once the user selects a location and hits "Ok" the MACRO creates a new folder using a list in excel (E26:E75) that the user can modify so they don't have to manually create folders and rename them.
I need the MACRO to also create and save an excel work book in each folder with the same name as the newly created folder. On a side note, in the same workbook that the users enter the list of folders they want created there is another worksheet/tab that has the a templated worksheet that needs to be saved as a workbook in each newly created folder. Currently, I am asking for help putting an empty workbook in each folder, once this is done each user can go in to each folder and newly created workbook and copy/past the templated worksheet and update as necessary.
Big picture, creating a folder for each "step" with an applicable "workpaper" in each folder. Each "workpaper" should have the same basic template format and stock information but some cells/information will change based on "steps" for each "workpaper".
Any help or guidance would be appreciated, thanks.
Current MACRO:
CODE#
Sub Create_Folders()
'Macro to create folders from list in excel
Dim R As Range
Dim RootFolder As String
RootFolder = GetFolder() 'Uses GetFolder module to prompt user to select location for folders
For Each R In Range("E26:E75") 'Targeted Cells from which to create folders based on
If Len(R.Text) > 0 Then
On Error Resume Next
MkDir RootFolder & "" & R.Text
On Error GoTo 0
End If
Next R
End Sub
Function GetFolder() As String
'Macro to prompt users to select location for files or folders
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
Last edited:
