Copy entire file using VBA

[Rob]

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

 

[Ron de Bruin]

Hi Rob

Worksheets.copy
or
Sheets.copy

 

[Simon Lloyd]

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

HTH

Simon

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

 

[Patrick Molloy]

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

 

[Nigel]

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]

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]

Hi Rob

Try to use Patrick suggestion (SaveAs)

Dave Peterson posted this about your problem
***************************************
I've had (still have!) this problem. Sometimes minimizing/maximizing the
worksheet window will help. Sometimes minimizing/maximizing the application
window helps.

Sometimes removing the filters and reapplying help.

Sometimes closing and reopening the workbook (and closing and restarting excel)
works.

And sometimes nothing works.

One of the last times this happened at work the "victim" emailed me a copy.
When I opened that copy, I could see the dropdown arrows. I saved it and
emailed it back. He could see them in the return email.

We both use win2k and xl2k.

So, my response is, aggggggggggggggghhhhhhhhhh!

(I have no idea why it happens or what can fix it (consistently!).

 

[Rob]

Thanks everyone, I have plenty to work with.

regards, Rob