excelfilesaveexportcreation

Create new Excel workbook blind from a single sheet in an open workbook

I'm trying to create a new Excel file from an open sheet. I've been through several solutions here but none of them quite get to what I need. I'm not very good with Excel file interaction out on disk. This is Excel 2010.

  1. I have some VBA code that writes out relevant data to a new sheet (let's say Sheet3).
  2. Then, I would like to write out all of Sheet3 to a new Excel file "output.xlsx" in a given folder (let's say F:\test)
  3. I was hoping the process would create the xlsx file blind, populate Sheet1, save, and close the file without the user ever seeing the open output.xlsx file.

EDIT #2: working with this exact code:

Dim FPath As String, FName As String, filenamex As String
Dim NewBook As Workbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False

FPath = "F:\test\"
FName = "output" & ".xlsx"
filenamex = FPath & FName
Set NewBook = Workbooks.Add
Worksheets("Sheet3").Range("A1:N100").Copy 
Destination:=NewBook.Worksheets("Sheet1").Range("A1")
Application.CutCopyMode = False
NewBook.Close savechanges:=True, Filename:=filenamex

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Result #2: (a) The excel file output.xls is created and SAVED (thank you) (b) Three sheets are created as exists in the original file (c) All three sheets are still blank. I only need Sheet3 copied from the original file.

Thanks

Solution

  • Primarily, you can just turn off ScreenUpdating.

    Sub copy_to_new()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim FPath As String, FName As String, filenamex As String
Dim NewBook As Workbook, oldBook as Workbook

Set oldBook = ActiveWorkbook 
'Or use `ThisWorkbook` if you're running this code from the workbook you want to copy from.

FPath = "F:\test\"
FName = "output.xls"
filenamex = FPath & FName
Set NewBook = Workbooks.Add
oldBook.Worksheets("Sheet3").Range("A1:N100").Copy Destination:=NewBook.Worksheets("Sheet1").Range("A1")
Application.CutCopyMode = False

' We can use the .Close command to close and save the file in one line
NewBook.Close savechanges:=True, Filename:=filenamex

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub