Save specific sheet to new workbook without opening - Excel

Michael Noze
Michael Noze used Ask the Experts™
on
Hi Experts,

I'd like to know if there is a way to copy an Excel sheet to a new workbook without having to open it? My source file is a xlsm and when I run my macro, a pop-up keep appearing. Is there a solution to this?

Sub Sheet_SaveAs()

Dim wb As Workbook

Sheets("Output").Copy

Set wb = ActiveWorkbook

With wb
.SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
.Close False
End With

End Sub

Open in new window


Thank you.
pop-up.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Analyst Assistant
Commented:
Michael

I don't know of any way to copy a sheet from an Excel workbook without opening the workbook.

However there could be ways to prevent the pop-up appearing.

The first way would be to suppress alerts while you are saving the new workbook.
Application.DisplayAlerts = False
With wb
    .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
    .Close False
End With
Application.DisplayAlerts = True

Open in new window


Another way, which might not be appropriate, would be to specify the file format to save as to be as a macro enabled workbook.
With wb
    .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd"), xlOpenXMLWorkbookMacroEnabled
    .Close False
End With

Open in new window

Michael Noze

Author

Commented:
Hi,

That's perfect.

Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial