Martyn, "sh.Cop" should be sh.Copy"
Also, if the file exists, you will be prompted to replace and if you cancel
you get an error. To avoid the prompt use "Application.DisplayAlerts =
False". Set back to True when done as it is a permanent session change. If
you don't want to replace existing files, you will have to use Dir(sh.name &
".xls") to test if the file is there (returns blank or the file name)
Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on
VB macros for Excel
"Martyn" wrote in message
...
Bob,
Thanks
Used your code on Sheet1 and Received a compile error saying "Invalid
outside procedure".
p.s.: I'm on WinXP with Office2K
Martyn
"Bob Phillips" wrote in message
...
Martyn,
Here is an example to do what you want
Set shOrig = Activesheet
For Each sh In ActriveWorkbook.Worksheets
sh.Cop
ActiveWorkbok.SaveAs FileName:= "C:\Samples\" & sh.name & ".xls"
ActiveWorkbook.Close
Next sh
shOrig.Activate
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Martyn" wrote in message
...
Hi,
The below macro manages to create seperate sheets to the workbook
using
the
A:A column data:
--------------------------
Sub SayfaYarat()
On Error Resume Next
Dim rng As Range
Dim cll As Range
Dim NewBook As Workbook
Dim cnt As Integer
Dim ShtCnt As Integer
Set rng = Range("A1", Range("A1").End(xlDown))
If IsEmpty(rng.Cells(1, 1)) Then
Exit Sub
End If
Set NewBook = ActiveWorkbook
cnt = 1
ShtCnt = ActiveWorkbook.Sheets.Count
Do
With NewBook
.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
ActiveSheet.Name = rng.Cells(cnt, 1).Value
cnt = cnt + 1
End With
Loop Until NewBook.Worksheets.Count - ShtCnt = rng.Rows.Count
End Sub
-----------------------------------
Considering a workbook having lots of pages, I need to modify this
macro
so
that when executed it will save each sheet to C:\Samples directory as
seperate Workbooks with its sheets name (i.e.: Sheet1.xls,
Sheet2.xls....etc). Is it possible?
I'd appreciate other solution suggestions too...
TIA
Martyn