Copy a sheet and save as new file, without opening the file

**Chinnick** · 09-12-2009, 05:42 PM

is it possible to copy one sheet in a workbook onto a new workbook then save it but not open the file?
I know i can open, save then close all in vba, but is there a way to do it in the background?

my current code looks like this

BidFile = Workbooks("Bid Sheet Creator").Sheets("Bid Sheet").Rows(3).Columns(2)
Application.CutCopyMode = False
Sheets("Bid Sheet").Copy
ActiveSheet.Name = BidFile

So i am copying my "Bid Sheet" to a new file and giving it a name. I have code below this saving the file then closing it but i am wondering if there is a way to change my above code to do it all in the background?

Thanks in advance

**shg** · 09-12-2009, 05:52 PM

In VBA you can create a new, hidden instance of Excel, open the file, copy the sheet, save the file, and quit the hidden instance.

I know of no way to copy a single sheet to a new workbook without opening the workbook.

**Chinnick** · 09-12-2009, 06:18 PM

How would i go about opening the hidden excel, sorry i am all self taught in this stuff and haven't come across this before.

**shg** · 09-12-2009, 06:35 PM

Something like this:

Sub x()
    With New Excel.Application
        .Open "C:\myPath\MyWorkbook.xls"
        .Worksheets("Bid Sheet").Copy
        With .ActiveSheet
            .Name = .Range("B3").Value
        End With
        .ActiveWorkbook.SaveAs "C:\myOtherPath\someName.xls"
        .ActiveWorkbook.Close                       ' the new workbook
        .ActiveWorkbook.Close SaveChanges:=False    ' the old workbook
        .Quit
    End With
End Sub

**shg** · 09-13-2009, 05:00 PM

Or, more simply,

Sub x()
    With Application
        .ScreenUpdating = False
        .Workbooks.Open "C:\myPath\MyWorkbook.xls"
        .Worksheets("Bid Sheet").Copy
        With .ActiveSheet
            .Name = .Range("B3").Value
        End With
        .ActiveWorkbook.SaveAs "C:\myOtherPath\someName.xls"
        .ActiveWorkbook.Close                       ' the new workbook
        .ActiveWorkbook.Close SaveChanges:=False    ' the old workbook
        .ScreenUpdating = True
    End With
End Sub