Easy way to split Excel file into multiple files

Posted on 2005-03-10
Medium Priority
Last Modified: 2008-01-09
We frequently have a nationwide Excel report that we have to split into separate spreadsheets, one for each territory based on a column in the spreadsheet.  I know I could write code to do it, but is there an easier way?
Question by:JohnBPrice
LVL 12

Expert Comment

ID: 13506373
Copy paste......

Sorry if that sounds daft... but selecting certain bits and pasting em in a new spreadsheet would be an easy way wouldn't it?

Other way
Import it in access and get them apart with queries.
You only need to create these queries once seeing you import every new excelfile in the same db
LVL 35

Accepted Solution

mvidas earned 1500 total points
ID: 13506613
Hi John,

I know you said you could write code to do it, but unless you do want to do it manually each month, or use kneH's access method, you could just run a macro each month.  And if you don't feel like writing it, use the code I just wrote for you:

Sub SplitIntoMultipleSheetsBasedOnColumn()
 Dim TheColumn As Range, CLL As Range
 Dim UniqVals() As Variant
 Dim FirstDataRow As Long, i As Long, iLB As Long, iUB As Long
 Set TheColumn = Columns("A")
 FirstDataRow = 2 'so that the header row(s) aren't turned into a sheet
 ReDim UniqVals(0)
 For Each CLL In Range(TheColumn.Cells(FirstDataRow), TheColumn.Cells(65536).End(xlUp))
  If Not InArray(UniqVals, CLL) Then
   UniqVals(UBound(UniqVals)) = CLL
   ReDim Preserve UniqVals(UBound(UniqVals) + 1)
  End If
 Next CLL
 iLB = 0
 iUB = UBound(UniqVals) - 1
 ReDim Preserve UniqVals(iUB)
 Application.ScreenUpdating = False
 For i = iLB To iUB
  Set CLL = FoundRange(TheColumn, UniqVals(i))
  Workbooks.Add -4167
  If FirstDataRow > 1 Then Range(TheColumn.Cells(1), TheColumn.Cells _
   (FirstDataRow - 1)).EntireRow.Copy ActiveSheet.Range("A1")
  CLL.EntireRow.Copy ActiveSheet.Range("A" & FirstDataRow)
 Next i
 Application.ScreenUpdating = True
End Sub
Public Function InArray(ByRef vArray(), ByVal vValue) As Boolean
 Dim i As Long, iLB As Long, iUB As Long
 iLB = LBound(vArray)
 iUB = UBound(vArray)
 For i = iLB To iUB
  If vArray(i) = vValue Then InArray = True: Exit Function
 Next i
 InArray = False
End Function
Function FoundRange(ByVal vRG As Range, ByVal vVal) As Range
 Dim FND As Range, FND1 As Range
 Set FND = vRG.Find(vVal, LookIn:=xlValues, LookAt:=xlWhole)
 If Not FND Is Nothing Then
  Set FoundRange = FND: Set FND1 = FND: Set FND = vRG.FindNext(FND)
  Do Until FND.Address = FND1.Address
   Set FoundRange = Union(FoundRange, FND): Set FND = vRG.FindNext(FND)
 End If
End Function

LVL 15

Expert Comment

ID: 13506812
If the separation is strictly for visual clarity....another option is to use the Autofilter option on the Data...Filter menu. This will put a dropdown on each column  header and allow you to filter your list in place for everything matching the values you select.

This could also be helpflu if you choose to do the cut and paste method as it will get all rows together on one screen.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 16

Author Comment

ID: 13508067
I was rather hoping someone knew of a burst-type feature similar to that in many reporting tools.
LVL 16

Author Comment

ID: 13534176
Well, I didn't get what I hoped for, but thanks for writing the code for me mvidas.
LVL 35

Expert Comment

ID: 13535251
Since excel doesn't really have anything to burst the file as you need, this will at least get you by.  Is it possible for you to get one of these other reporting apps? Perhaps export the excel file into the format of the other app, burst it there, then convert the bursted files back into excel? You'd have to do a time-analysis of the two methods, but it could be worth looking into.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Web hosting control panels were first developed to make it faster and easier for most users to set up and operate websites. The graphical user interface (GUI) allows users to perform tasks by pointing and clicking rather than typing highly specific…
There are literally thousands of Exchange recovery applications out there. So how do you end up picking one that’s ideal for your business & purpose? By carefully scouting the product’s features, the benefits it offers you, & reading ample reviews f…
Viewers will learn how to use the Hootsuite Dashboard.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question