Help required on macro for excel
 

Hello,
I have an excel file consisting of around 60 sheets. Each sheet i
named after the code of a customer/dealer. Now, I want to save eac
sheet as a new excel file with the name of the file as the name of th
sheet itself. (ie. I want to have 60 new excel files with eac
customer/dealer details sheet as one file). If one sheet is name
A0005, then this sheet should be saved as A0005.xls.

I would be very grateful if anyone of you can write a macro here whic
will allow me to be able to save such sheets as a new excel file.

Thank you very muc

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

Help required on macro for excel
 

Hi

You could try something like


UNTESTED CODE

Sub Macro1()

Dim ws As Worksheet


For Each ws In Worksheets
ws.Activate
wsname = ws.Name

Sheets(ws.Name).Select
Sheets(ws.Name).Copy
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\Ken Macksey\My Documents\" & ws.Name &
".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Next


End Sub


HTH

Ken


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004

Help required on macro for excel
 

Hi

After a chance to test it, I added a few lines to make it run a little
better.



Sub Macro1()

Dim ws As Worksheet


Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Activate
wsname = ws.Name

Sheets(ws.Name).Select
Sheets(ws.Name).Copy
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\Ken Macksey\My Documents\" & ws.Name &
".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

ActiveWorkbook.Close

Next


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End sub


Ken


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004

Help required on macro for excel
 

Hi Ken,
Yes, you improved the previous solution for speed,
you might also look at....

For a more robust solution see
Save each worksheet separately to multiple workbooks
Saveas, Save each worksheet as a separate workbook
http://www.mvps.org/dmcritchie/excel...tipleXLSfromWB

Takes care of worksheet name having same name as workbook,
and eliminates sheet1 to make it look more realistic.

Of course any links such hyperlinks or formulas will likely fail.

Provides a means of checking how big some worksheets really are,
as an additional benefit.

I don't imagine many other will find this in the archives looking for their
own solutions because the subject line has very little to do with the question.

Excel (yes, ho hum, so what, that's what these newsgroups are),
Help (ho hum), macro (yes, you're in the right group that's what this
group is all about).

The subject does not describe what you need. The only thing your
subject told is that you need a macro and know how to install and use one,
but then you poste in programming so that is expected.

Anyway I didn't find something myself in the archives -- way too many unrelated hits.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ken Macksey" wrote in message ...
Hi

After a chance to test it, I added a few lines to make it run a little
better.

Help required on macro for excel
 

Sorry, Ken I was thinking your were the original poster,
places like ExcelForum with faceless id's and MS CDO
with anonymous@ makes it difficult to tell who is who anymore.
Or as the other's would say, I'm just getting older (but not wiser).

David McRitchie

Help required on macro for excel
 

thank you very much guys. I will definitely try these out

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