I have an excel file with over 20 worksheets and I need to create a with each
of those sheets. Does somebody know how to do it in practical terms. I know
how to do it one by one but it takes time.
thanks.

hi,
here is a "save range" macro i wrote for myself some years back. it might do
what you want. select the sheet and run the macro.
Sub mac1SaveRange()

'Macro written by FSt1 4/27/03

Dim cnt As Long
Dim cell As Range
On Error GoTo err1

MsgBox "You have selected range" & Selection.Address
If Selection.Cells.Count = 1 Then
If MsgBox("You have selected only one cell. Continue?????", vbYesNo,
"Warning") = vbNo Then
Exit Sub
End If
End If
cnt = 0
For Each cell In Selection
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data in the selected range. Continue?!?!?!?!?",
vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
'ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
err1:
MsgBox ("Need a range to save, Speedbrain.")
Exit Sub
End Sub

regards
FSt1

"SANCAKLI" wrote:

I have an excel file with over 20 worksheets and I need to create a with each
of those sheets. Does somebody know how to do it in practical terms. I know
how to do it one by one but it takes time.
thanks.

You could use a macro:

Option explicit
sub testme()
dim wks as worksheet
for each wks in activewindow.selectedsheets
wks.copy 'to a new workbook
with activesheet
.parent.saveas filename:="C:\temp\" & .name & ".xls", _
fileformat:=xlworkbooknormal
.parent.close savechanges:=false
end with
next wks
end sub

Click on the first sheet tab to be saved and ctrl-click on subsequent. Then run
the macro.

And change the folder to what you need. I used C:\temp.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




SANCAKLI wrote:

I have an excel file with over 20 worksheets and I need to create a with each
of those sheets. Does somebody know how to do it in practical terms. I know
how to do it one by one but it takes time.
thanks.

--

Dave Peterson