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

fabriciofcmiranda

Board Regular
Joined
Jun 27, 2006
Messages
62
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
This will copy the active sheet to a new workbook, then prompt for a filename , save and close.
Code:
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

Board Regular
Joined
Jun 27, 2006
Messages
62
Hi, everyone,

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

Code:
    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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Fabricio

Try this.
Code:
    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
 

Forum statistics

Threads
1,136,649
Messages
5,676,991
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top