How can I...

R

RMort

I need to generate a separate xls file for each page in a workbook.
Can this be done.
I know that you can do it in Word with a Macro (thanks to the Word
Discussion Group)
But now I need to perform the same function in Excel.
Any help will be greatly appreciated.

Thanks in advance,
Rick
 
D

Dave Peterson

Option Explicit
Sub testme()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" & .Name, _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
Next wks

End Sub

Adjust the path to what you want.
 
R

RMort

That separates each sheet of the workbook into a file.
I need each page break of one sheet to be in a different file.
Thanks for your answer,
Rick
 
D

Dave Peterson

I stole a bunch of code from Tom Ogilvy's post:

http://groups.google.com/groups?threadm=ufpc9nulCHA.1568@tkmsftngp07

I modified it to only care about horizontal page breaks, but you could modify it
to include the vertical page breaks, too.

Option Explicit
Sub testme01()

Dim HorzPBArray()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim TopRow As Long
Dim i As Long

Set curWks = ActiveSheet
curWks.DisplayPageBreaks = False

ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""" & _
ActiveSheet.Name & """)"

ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""" & _
ActiveSheet.Name & """)"

i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve HorzPBArray(1 To i)
HorzPBArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend

ReDim Preserve HorzPBArray(1 To i - 1)

Set newWks = Workbooks.Add(1).Worksheets(1)

TopRow = 1
For i = LBound(HorzPBArray) To UBound(HorzPBArray)
newWks.Cells.Clear
curWks.Rows(TopRow & ":" & HorzPBArray(i) - 1).Copy _
Destination:=newWks.Range("a1")
newWks.Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" & "Page" & i, _
FileFormat:=xlWorkbookNormal
TopRow = HorzPBArray(i)
Next i

newWks.Parent.Close savechanges:=False

End Sub
 
G

Gord Dibben

Rick

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP
 
R

RMort

Gord,



Gord Dibben said:
Rick

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP
Click to expand...
 
R

RMort

ok that works great
it there anyway to make it so the file names can be sorted correctly
right now they go:

Page1
Page10
Page100
Page101
etc

to retain the correct order I need them to be in a normal order

Page1
Page2
Page3
etc

I've been trying a few things
but I keep getting errors (I know very little about VBA)

Thanks in advance,
Rick
 
R

RMort

Gord,

This only serarates the sheet tabs into separate files.
I need each page break to be a separate file.

thanks,
Rick
 
D

Dave Peterson

How about:

newWks.Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" _
& "Page" & format(i,"0000"), FileFormat:=xlWorkbookNormal

You'll end up with:

Page0001
Page0002
....



ok that works great
it there anyway to make it so the file names can be sorted correctly
right now they go:

Page1
Page10
Page100
Page101
etc

to retain the correct order I need them to be in a normal order

Page1
Page2
Page3
etc

I've been trying a few things
but I keep getting errors (I know very little about VBA)

Thanks in advance,
Rick
Click to expand...
 
Top