View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Bernard Liengme Bernard Liengme is offline
external usenet poster
 
Posts: 4,393
Default saving 3 worksheets in a workbook to a separte workbook

Chip's method is better - the macro stays in the original workbook and is
not duplicated in the second one
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"bigjim" wrote in message
...
Interesting approach. I never thought of doing it that way. I'll give it
a
try.

Thanks,



"Bernard Liengme" wrote:

Here is a start:

Sub Macro3()
ActiveWorkbook.SaveCopyAs "NewBook.xlsm"
Workbooks.Open Filename:="NewBook.xlsm"
For Each ws In Worksheets
If ws.Name = "temp1" Or ws.Name = "temp2" Or ws.Name = "temp3" Then
'do nothing
Else
ws.Delete
End If
Next
Workbooks("NewBook.xlsm").Save

End Sub

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"bigjim" wrote in message
...
I'm using Excel 2003 and I want use vba to save 3 of the sheets in a
workbook
to a new workbook with a new name generated each time. I can save one
but
I
can't figure out how to save three. For example, I want to copy three
sheets
named temp1, temp2, and temp3 to a new workbook created and named in
the
original workbook, so that workbook 2 will contain only copies of
sheets,
temp1, temp2, and temp3. Any help will be greatly appreciated.