CODEWITHSUNDEEP
excelvbadefaultrenaming
Wabbage
Wabbage

Reputation: 437

VBA: Rename a copied worksheet

I have this line of code that copies a worksheet to a new workbook:

OrderForm.Copy ' copy worksheet to new workbook

The new workbook shows up as Bookn.xlsx: enter image description here

When saving this is what shows up: enter image description here

I'm okay if it shows up as Bookn on the title bar and I don't want to automatically save it, but what I want is to have a default name (also default to .xlsx) when the user decides to save it, like this:

enter image description here

Upvotes: 0

Views: 193

Answers (1)

Mikegrann
Mikegrann

Reputation: 1081

Use the BeforeSave event to capture the user saving, then show your own dialog with an initial value (like so):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As String

    Cancel = True

    fName = Application.GetSaveAsFilename("Default Name", _
        "Excel Workbook (*.xlsx), *.xlsx," & _
        "Macro Enabled Workbook (*.xlsm), *xlsm")
    ThisWorkbook.SaveAs fName
End Sub

Upvotes: 1

Related Questions