I would like to merge excel file, save as new file and rename the file based on user input in cell L14. It is run ok. However, I would like to have message box tell user "You've not input the file name" in case user forgot to input in cell L14. Very appreciate any help on this.
Sub MergeFile ()
Dim WB As Workbook
Dim WS as Worksheet
Dim FileName as String
Dim FilePath as String
Set WB = Workbook.Add
FilePath = "C:\Users\Desktop\SaveFile"
FileName = Thisworkbook.Worksheets("Template").Range("L14").Text
For Each WS In Thisworkbook.Worksheets
If WS.Name <> "Template" Then
WS.Copy before:=WB.Sheets(1)
End If
Next
Activeworkbook.SaveAs FileName:=FilePath & "\" & FileName
MsgBox ("Done!")
Activeworkbook.Close
End Sub
Aucun commentaire:
Enregistrer un commentaire