Macro Help

Kyosti

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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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
442
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
84
I only now was able to get back to this process. @JoeMo , your suggestion worked like a charm thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,369
Messages
5,414,048
Members
403,514
Latest member
Vivek pare

This Week's Hot Topics

Top