Macro Help

Kyosti

Board Regular
Joined
Jun 2, 2008
Messages
83
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,644
Untested - may need some tweaking for path separator and spacing within the file name.
Code:
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

Active Member
Joined
Jun 4, 2015
Messages
434
Code:
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

Board Regular
Joined
Jun 2, 2008
Messages
83
I only now was able to get back to this process. @JoeMo , your suggestion worked like a charm thank you.
 

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top