I have a workbook that has approximately 30 worksheets in it. Needless to
say this is a very large workbook. If I try and send it to the (30)
departments concerned it takes a while to open etc. What I would like to do
(By way of a Macro) is to set up 30 workbooks with each name of the workbook
from the worksheet tab..

Can do?????????

Sub Multfiles()
Dim wks as Worksheet
Application.DisplayAlerts = False
For each wks in Worksheets
newfilename=wks.name
ActiveWorkbook.SaveAs FileName:="C:\" & newfilename & ".xls", _
FileFormat:=xlExcel4, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Next wks

Application.DisplayAlerts = True

End Sub



--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Beep Beep" wrote:

I have a workbook that has approximately 30 worksheets in it. Needless to
say this is a very large workbook. If I try and send it to the (30)
departments concerned it takes a while to open etc. What I would like to do
(By way of a Macro) is to set up 30 workbooks with each name of the workbook
from the worksheet tab..

Can do?????????

See Ron deBruin's code for this

http://www.rondebruin.nl/copy6.htm


"Beep Beep" wrote:

I have a workbook that has approximately 30 worksheets in it. Needless to
say this is a very large workbook. If I try and send it to the (30)
departments concerned it takes a while to open etc. What I would like to do
(By way of a Macro) is to set up 30 workbooks with each name of the workbook
from the worksheet tab..

Can do?????????