Copy a sheet to a New workbook (of one sheet) with

Question

I would like to copy a sheet from ActiveWorkbook, to a New Created workbook. The New created workbook, will contains the copied sheet ONLY.

I also don't want it to open while copiying the sheet. I want to copy the sheet to the new created workbook silently.

If I do something like the following, the new created book is with 3 sheets, not one only, and it's opening while copiying. and also it's asking me if i want to saved it with Macro, while I only want to copy the sheet1, so no need for any macro with it, How to fix that to fits my needs ?

ThisWorkbook.Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs "C:\DestinationWb.xlsx", FileFormat:=51

Answer 1

Hello, I just tried the code you provided, it still opens the book for about 1-2 seconds and close it. the user will clearly see that the book is opened. is there any other way, not forcely the sheet.copy or it's the only way to copy ? – JustGreat 50 mins ago

The only way I can think of in such a scenario is to do the following.

Logic:

1. Use the .SaveCopyAs method to save a copy of the existing workbook. You can read more about .SaveCopyAs HERE
2. Create another instance of Excel and Hide it
3. Open the copy in that instance
4. Delete all sheets except the one which you want.
5. Save and Close and finally quit the Excel instance.

Code:

Sub Sample()
    Dim thisWb As Workbook

    '~~> New File Name
    Dim NewFile As String
    NewFile = "C:\Users\routs\Desktop\New folder\DestinationWb.xlsx"

    '~~> Sheets that you want to copy across
    Dim SheetToCopy As String
    SheetToCopy = "Sidd"

    Set thisWb = ThisWorkbook

    '~~> Save a copy of the current workbook to the new path
    thisWb.SaveCopyAs NewFile

    '~~> Create a new Excel instance and keep it hidden
    Dim tmpExcelApp As Object
    Dim ws As Object, thatWb As Object
    Set tmpExcelApp = CreateObject("Excel.Application")
    tmpExcelApp.Visible = False

    '~~> Open the copy file in hidden instance
    Set thatWb = tmpExcelApp.Workbooks.Open(NewFile)

    '~~> Delete all sheets except the one we copied
    tmpExcelApp.DisplayAlerts = False
    For Each ws In thatWb.Worksheets
        If ws.Name <> SheetToCopy Then ws.Delete
    Next ws
    tmpExcelApp.DisplayAlerts = True

    '~~> Save and close
    thatWb.Close (True)

    '~~> Quit Excel Instance
    tmpExcelApp.Quit

    MsgBox "Done"
End Sub

Answer 2

The newly created workbook will have to be open - otherwise how would you save it? - but toggling Application.ScreenUpdating might facilitate the "silent" copy you're looking for. Toggling Application.DisplayAlerts will also suppress alerts as needed.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ThisWorkbook.Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs FileName:="C:\DestinationWb.xlsx", FileFormat:=51
ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True