copy worksheet to new workbokk & save
 

I need to copy several worksheets to a new workbook, save the new workbook
with a particular name and then close the new workbook (keeping the existing
workbook open).

the code i have works fine for one workbook (though i haven't gotten to the
closing the workbook yet). how can i adapt it to include the other
worksheets (sheets 4, 5 and 11 in this case)?

<BEGIN VB CODE
Dim dir As String
Dim wkbk As Workbook

ActiveSheet.Copy
Set wkbk = ActiveWorkbook
With wkbk.Sheets(1).UsedRange
..Value = .Value
End With

dirstr = ".\orders"
On Error Resume Next
MkDir dir
On Error GoTo 0
wkbk.SaveAs dir & "\" & workOrderNumber & ".xls"
<END VB CODE

copy worksheet to new workbokk & save
 

Dim dir As String
Dim wkbk As Workbook, sh as Worksheet

worksheets(Array("Sheet4","Sheet5","Sheet11")).Cop y
Set wkbk = ActiveWorkbook
for each sh in wkbk.Worksheets
with sh.UsedRange
.Value = .Value
End With
Next

dirstr = ".\orders"
On Error Resume Next
MkDir dirstr '<=== typo fixed
On Error GoTo 0
' typo in next line fixed
wkbk.SaveAs dirstr & "\" & workOrderNumber & ".xls"
<END VB CODE

--
Regards,
Tom Ogilvy

"Gixxer_J_97" wrote in message
...
I need to copy several worksheets to a new workbook, save the new workbook
with a particular name and then close the new workbook (keeping the
existing
workbook open).

the code i have works fine for one workbook (though i haven't gotten to
the
closing the workbook yet). how can i adapt it to include the other
worksheets (sheets 4, 5 and 11 in this case)?

<BEGIN VB CODE
Dim dir As String
Dim wkbk As Workbook

ActiveSheet.Copy
Set wkbk = ActiveWorkbook
With wkbk.Sheets(1).UsedRange
.Value = .Value
End With

dirstr = ".\orders"
On Error Resume Next
MkDir dir
On Error GoTo 0
wkbk.SaveAs dir & "\" & workOrderNumber & ".xls"
<END VB CODE

copy worksheet to new workbokk & save
 

Outstanding! Thanks Tom


"Tom Ogilvy" wrote:

Dim dir As String
Dim wkbk As Workbook, sh as Worksheet

worksheets(Array("Sheet4","Sheet5","Sheet11")).Cop y
Set wkbk = ActiveWorkbook
for each sh in wkbk.Worksheets
with sh.UsedRange
.Value = .Value
End With
Next

dirstr = ".\orders"
On Error Resume Next
MkDir dirstr '<=== typo fixed
On Error GoTo 0
' typo in next line fixed
wkbk.SaveAs dirstr & "\" & workOrderNumber & ".xls"
<END VB CODE

--
Regards,
Tom Ogilvy

"Gixxer_J_97" wrote in message
...
I need to copy several worksheets to a new workbook, save the new workbook
with a particular name and then close the new workbook (keeping the
existing
workbook open).

the code i have works fine for one workbook (though i haven't gotten to
the
closing the workbook yet). how can i adapt it to include the other
worksheets (sheets 4, 5 and 11 in this case)?

<BEGIN VB CODE
Dim dir As String
Dim wkbk As Workbook

ActiveSheet.Copy
Set wkbk = ActiveWorkbook
With wkbk.Sheets(1).UsedRange
.Value = .Value
End With

dirstr = ".\orders"
On Error Resume Next
MkDir dir
On Error GoTo 0
wkbk.SaveAs dir & "\" & workOrderNumber & ".xls"
<END VB CODE