Copy two sheets from workbook & create new workbook

M

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!
 
J

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
 
D

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
 
M

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top