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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Let’s list some of the technologies that enable smooth teleworking. 
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

762 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