Copy a sheet to a New workbook (of one sheet) without opening it

2
votes

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
2
excelvba
Does Sheet1 have VBA code-behind? If so, the document module (i.e. the corresponding VBA project component) is copied along with the Excel worksheet; if you need that code, the FileFormat you want is xlOpenXMLWorkbookMacroEnabled; that 51 is the underlying value for xlOpenXMLWorkbook. I suspect that might be why Excel is warning about saving it with macros in the first place. – Mathieu Guindon
nope it doesn't, it's just a sheet with text formatted with colors and date format in some cells etc...but not vba behind. – JustGreat
Why not keep it opened and prompt whether to close it instead? It's there already, no need to close it if you're going to re-open the same file... – Mathieu Guindon
Worksheet.Copy creates the new destination workbook and activates that workbook - there's nothing to open and if you copied a worksheet the destination workbook is going to be opened whether you want it or not... still makes no sense to close it only to prompt the user if they want to open it. You're fighting the side-effects of Worksheet.Copy, the very same ones that even allow you to close the created workbook... – Mathieu Guindon
See the documentation, and note that a Workbook object you can interact with using the Excel object model in VBA, can only ever exist as an element of the Application.Workbooks collection: if it's not "opened", it doesn't exist as far as VBA is concerned. You could conceivably use OpenXML to create the file from scratch without the Excel object model, but since you're using VBA and you're already hosted in Excel it makes little sense to do so, and massively complexifies what should be a fairly simple thing. – Mathieu Guindon

2 Answers

3
votes

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
3
votes

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