is it possible to change this macro to save each sheet seperately?

[Martyn]

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 Phillips]

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]

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 Flanagan]

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]

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