About Us

Gixxer_J_97[_2_]

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I save only active worksheet without save all of worksheet	droth	Excel Worksheet Functions	2	February 21st 09 05:11 PM
specified time / copy worksheet + save	Robert B.	Excel Worksheet Functions	3	November 2nd 07 11:10 AM
How to save a file without overwrite or save a copy?	SettingChange	Setting up and Configuration of Excel	1	November 3rd 05 02:10 AM
Automatically save a copy of worksheet from C: to Network Drive	doosbug	Excel Discussion (Misc queries)	1	May 3rd 05 06:23 PM
Worksheet Buttons (Save, Save As, Cut, Paste, etc.) Not Working	SuzieQ12345	Excel Worksheet Functions	5	January 21st 05 02:57 PM

Menu

About Us