Copy entire file using VBA
 

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

Copy entire file using VBA
 

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

Copy entire file using VBA
 

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

Copy entire file using VBA
 

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

Copy entire file using VBA
 

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

Copy entire file using VBA
 

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