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

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

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

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

Thank you Bob, also thanks to Bob Phillips...
Now the code operates with no problem at all.
Martyn


"Bob Flanagan" wrote in message
...
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