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?????????

Michael;

Close but no cigar. I probably wasn't clear enough. What your macro is
doing is just taking the one ws and giving it a name of all the other tabs.
Actually what I want to do is take the current tab (30) and create a workbook
for each tab in the current workbook with the data in that individual tab.
Each tab is already named such as (5329;5420;5421;6403 etc etc.

"Michael" wrote:

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?????????

Not really, if you open your c: drive you will see 30 different workbooks
named after each one of the worksheets and it contains the data under the
that sheet.

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

Michael Arch.




"Beep Beep" wrote:

Michael;

Close but no cigar. I probably wasn't clear enough. What your macro is
doing is just taking the one ws and giving it a name of all the other tabs.
Actually what I want to do is take the current tab (30) and create a workbook
for each tab in the current workbook with the data in that individual tab.
Each tab is already named such as (5329;5420;5421;6403 etc etc.

"Michael" wrote:

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?????????

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?????????

Duke

Thank you; thank you; thank you

Pretty impressive macro.

Frank

"Duke Carey" wrote:

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?????????

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?????????