lundi 22 juin 2020

If statement in renaming file

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