Excel 2000

I'm using the following code Sheets(1).Copy to copy a sheet from an opened
file to a new workbook, however I need to copy all the sheets to a new
workbook not just the first.

Is this possible and if so, can someone please provide sample code.

Thanks, Rob

Hi Rob

Worksheets.copy
or
Sheets.copy

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rob" wrote in message ...
Excel 2000

I'm using the following code Sheets(1).Copy to copy a sheet from an opened
file to a new workbook, however I need to copy all the sheets to a new
workbook not just the first.

Is this possible and if so, can someone please provide sample code.

Thanks, Rob

If you want specific files copied n records counted try this site!

HTH

Simon

http://www.sas-tips.com/vba/inputoutput.htm

--
Message posted from http://www.ExcelForum.com

Ron,

Thanks for the reply, a funny thing happens however! If I use either
worksheets.copy or sheets.copy and the file being copied has
Selection.AutoFilter set, the AutoFilter feature is copied albeit the drop
down arrows don't show. If I however, use worksheets(1).copy the AutoFilter
arrows show.

My issue now is because I include the (1) only a single sheet is copied.

Any thoughts?

Thanks, Rob


"Ron de Bruin" wrote in message
...
Hi Rob

Worksheets.copy
or
Sheets.copy

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rob" wrote in message
...
Excel 2000

I'm using the following code Sheets(1).Copy to copy a sheet from an
opened
file to a new workbook, however I need to copy all the sheets to a new
workbook not just the first.

Is this possible and if so, can someone please provide sample code.

Thanks, Rob

Wouldn't the simplest way be to simple SAVEAS...

OldName ="MyCurrentFile.xls"
NewName = "MyNewFile.xls"
DIM wb as Workbook

set wb = workbooks.open(OldName)
wb.SaveAs NewName


This leaves the workboook open, but renamed


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Rob" wrote in message
...
Excel 2000

I'm using the following code Sheets(1).Copy to copy a sheet from an opened
file to a new workbook, however I need to copy all the sheets to a new
workbook not just the first.

Is this possible and if so, can someone please provide sample code.

Thanks, Rob

Dim sno As Integer
For sno = 1 To Worksheets.Count
Worksheets(sno).Copy
' put your destination workbook and sheet location and paste code here
Next

"Rob" wrote in message
...
Excel 2000

I'm using the following code Sheets(1).Copy to copy a sheet from an opened
file to a new workbook, however I need to copy all the sheets to a new
workbook not just the first.

Is this possible and if so, can someone please provide sample code.

Thanks, Rob