Macro to export a sheet into a new workbook and save as

[fabriciofcmiranda]

Hi, there,

Sorry for asking such a dumb question, but I can't figure out how to set-up a macro that does the following:

1. Copy the specific sheet into a new workbook;

2. "Save as", so the user can choose the directory and the name of the file when saving it.

I'm sure it's really easy, but I couldn't program it or get it done through the "Record Macro" command.

Thank you very much!

 

[Norie]

This will copy the active sheet to a new workbook, then prompt for a filename , save and close.

Sub SaveSheet()
Dim ws As Worksheet
Dim wb As Workbook
Dim varFilename
    
    Set ws = ActiveSheet
    ws.Copy
    Set wb = ActiveWorkbook
    varFilename = Application.GetSaveAsFilename(ws.Name & ".xls", "Excel Files (*.xls),*.xls")
    If TypeName(varFilename) = "String" Then
        wb.SaveAs varFilename
        wb.Close
    End If
    
End Sub

 

[fabriciofcmiranda]

Thank you so much, Norie! It worked perfectly!
Best regards,
FM

 

[fabriciofcmiranda]

Hi, everyone,

I have the following code that copies a sheet into a new workbook and saves it:

    Dim ws As Worksheet
    Dim wb As Workbook
    Dim varFilename
    Set ws = ActiveSheet
    ws.Copy
    Set wb = ActiveWorkbook
    varFilename = Application.GetSaveAsFilename(ws.Name & ".xls", "Excel Files (*.xls),*.xls")
    If TypeName(varFilename) = "String" Then
        wb.SaveAs varFilename
        wb.Close
    End If

The problem is that, when I push "cancel" at the save as window that pops-up, it gives me an error message. When I cancel the process, I wanted the code to close the new workbook without saving it and return to the original workbook. Does anyone know how to program the code for it?

Thanks a lot!

Best regards,
Fabricio Miranda

 

[Norie]

Fabricio

Try this.

    Dim ws As Worksheet 
    Dim wb As Workbook 
    Dim varFilename 
    Set ws = ActiveSheet 
    ws.Copy 
    Set wb = ActiveWorkbook 
    varFilename = Application.GetSaveAsFilename(ws.Name & ".xls", "Excel Files (*.xls),*.xls") 
    If TypeName(varFilename) = "String" Then 
        wb.SaveAs varFilename 
        wb.Close 
    Else
         wb.Close False
    End If

 

[fabriciofcmiranda]

Norie, you're my savior! Thank you so much for your help (AGAIN!!!). Best regards, Fabricio.