View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Gixxer_J_97[_2_] Gixxer_J_97[_2_] is offline
external usenet poster
 
Posts: 206
Default 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