Macro Help

[Kyosti]

The following macro is designed to split several tabs within a sheet into individual files. What I would like to do is get this part of the macro: Format(Date, " BOB mm-yyyy" to prompt a dialog box once at the beginning of the macro to ask what is the additional name tag the files should use. This part of the file name is the same across every file it is only the first part of the file name that is unique. Can anyone help me?


Sub Filesplit()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Data" Then
sh.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & sh.Name & Format(Date, " BOB mm-yyyy") & ".xlsx", FileFormat:=51
ActiveWorkbook.Close False
End If
Next
End Sub

 

[JoeMo]

Untested - may need some tweaking for path separator and spacing within the file name.

Sub Filesplit()
Dim sh As Worksheet, fName As String
fName = InputBox("Enter file name prefix")
If fName = "" Then Exit Sub
fName = " " & fName & " " & Format(Date, "mm-yyyy")
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Data" Then
sh.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sh.Name & fName & ".xlsx", FileFormat:=51
ActiveWorkbook.Close False
End If
Next
End Sub

 

[Paul Ked]

Sub Filesplit()
    Dim sh As Worksheet, Nm As String
    Nm = InputBox("Please enter the name prefix...", "Name required")
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Data" Then
            sh.Copy
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & sh.Name & " " & Nm & Format(Date, " mm-yyyy") & ".xlsx", FileFormat:=51
            ActiveWorkbook.Close False
        End If
    Next
End Sub

 

[Kyosti]

I only now was able to get back to this process. @JoeMo , your suggestion worked like a charm thank you.

 

[JoeMo]

I only now was able to get back to this process. @JoeMo , your suggestion worked like a charm thank you.

You are welcome - thanks for the reply.