Splitting a File?

[Gmata]

Is there a way to Split a File?

For example you have a file with 10,000 Rows
Can you split it into 4 dif files with 2500 each?

 

[Dave Peterson]

Sure. You could cut and paste whatever rows you want into a different worksheet
in a new workbook and save that.

 

[Gmata]

Thanks, thats how i being doing it, but i was hoping for a quicker way thanks!

 

[Dave Peterson]

You could use a macro to do it, but I'm not sure it would be much quicker.

But if you have to do it lots of times, it may be easier to do it that way.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim pCtr As Long

myStep = 2500

Set wks = ActiveSheet
With wks
pCtr = 0
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = 1 To LastRow Step myStep
Set NewWks = Workbooks.Add(1).Worksheets(1)
.Rows(iRow).Resize(myStep).Copy _
Destination:=NewWks.Range("a1")
With NewWks.Parent 'the new workbook
pCtr = pCtr + 1
Application.DisplayAlerts = False
.SaveAs _
Filename:="C:\temp\" & Format(Date, "yyyymm") _
& "_Part" _
& Format(pCtr, "00") & ".xls", _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
.Close savechanges:=False
End With
Next iRow
End With

End Sub

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

I used column A to find the last used column in the worksheet. You may have to
change this--depending on your data.

And I saved to C:\temp\yyyymmdd_Part##.xls -- the folder C:\temp has to exist!