Copy two sheets from workbook & create new workbook

[Mike R.]

Hello,
I would like to copy two worksheets (formatting and all...) from one
workbook and create a new workbook with just those two sheets. Here is the
code I used to copy one worksheet, but I can not figure out how to do two. I
am not opposed to all new coding:

' save worksheet with this file name
FilePathName = Sheets("Data").Range("I23") & "\" &
Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$
& ".xls"
newsht = ActiveSheet.Copy
Set Wkb = ActiveWorkbook
Wkb.Sheets("Current Openings").Name = "Current Openings"
With ActiveWorkbook
.SaveAs Filename:=FilePathName
.Close False
End With

Thank you in advance for your help!

 

[Ron de Bruin]

Hi mike

See how i do it in this mail macro with a array
http://www.rondebruin.nl/mail/folder1/mail3.htm

I also create a temporary window to avoid a bug
It is up to you if you want to use it

 

[john]

not tested but something like this maybe:

Dim Wkb As Workbook
Dim FilePathName As String
With Sheets("Data")

FilePathName = .Range("I23") & "\" & _
.Range("I19") & "\" & _
.Range("I21") & "\" & _
Date$ & ".xls"

End With

'sheets you want to copy
'- change as required
Sheets(Array("Sheet1", "Sheet2")).Copy

Set Wkb = ActiveWorkbook

With Wkb

.SaveAs Filename:=FilePathName
.Close False

End With

 

[Don Guillett]

xl2003

Sub copyshtstonewwb()
mp = ActiveWorkbook.Name
'MsgBox mp
Sheets("Sheet4").Copy
Workbooks(mp).Sheets("sheet5").Copy _
after:=Sheets(Sheets.Count)
ActiveWorkbook.SaveAs Filename:="newfilename"
End Sub

 

[Mike R.]

Hi Ron,
For some reason, I am getting a "Subscript out of range" error on the Array
line of code. I did change the Sheets to my actual sheets I want to copy.

Help...
Mike

 

[Ron de Bruin]

Show me the code Mike that you use now


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Mike R.]

Here it is... thanks Ron.

Sub aUpdate_Daily()
Dim FilePathName As String

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
'We add a temporary Window to avoid the Copy problem
'if there is a List or Table in one of the sheets and
'if the sheets are grouped
With Sourcewb
Set TheActiveWindow = ActiveWindow
Set TempWindow = .NewWindow
.Sheets(Array("Sheet10", "Sheet13")).Copy
End With

'Close temporary Window
TempWindow.Close

Set Destwb = ActiveWorkbook

' 'Change all cells in the worksheets to values if you want
For Each sh In Destwb.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Destwb.Worksheets(1).Select
Next sh

'Save the new workbook/Mail it/Delete it
FilePathName = Sheets("Data").Range("I23") & "\" &
Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$
& ".xls"
With Destwb
.SaveAs FilePathName
.Close SaveChanges:=False
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

 

[Ron de Bruin]

Hi Mike

The sheet names are not correct then
Maybe there is a space before or after the sheet name

Check this out

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Mike R.]

got it... yep, I goofed the sheet names. Thanks Ron!