creating new workbook from one sheet

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi,
I would like to create a new xls file made of the content of ny current
Sheet2 (not sheet1 or 3).
is it possible?
Many thanks,
Dan
 
Hi Dan

Try this:

Sub CopySheet2ToNewWb()
Dim wbA As Workbook
Dim wbNew As Workbook
With Application
SheetsInWb = .SheetsInNewWorkbook
.SheetsInNewWorkbook = 1
.DisplayAlerts = False
.ScreenUpdating = False
End With

Set wbA = ThisWorkbook
Set wbNew = Workbooks.Add
wbA.Sheets("Sheet2").Copy wbNew.Sheets(1)
wbNew.Sheets("Sheet1").Delete
With Application
.SheetsInNewWorkbook = SheetsInWb
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Regards,
Per
 
Thank you Per

Per Jessen said:
Hi Dan

Try this:

Sub CopySheet2ToNewWb()
Dim wbA As Workbook
Dim wbNew As Workbook
With Application
SheetsInWb = .SheetsInNewWorkbook
.SheetsInNewWorkbook = 1
.DisplayAlerts = False
.ScreenUpdating = False
End With

Set wbA = ThisWorkbook
Set wbNew = Workbooks.Add
wbA.Sheets("Sheet2").Copy wbNew.Sheets(1)
wbNew.Sheets("Sheet1").Delete
With Application
.SheetsInNewWorkbook = SheetsInWb
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Regards,
Per
Click to expand...
 
Hi Per

Bob's way is easier to make a workbook of one sheet but
if you do it your way then there is no need to set the SheetsInNewWorkbook

This will add a workbook with one sheet

Set wbNew = Workbooks.Add(1)

Or use

Workbooks.Add(xlWBATWorksheet).
 
Back
Top