Split table into smaller tables & into different worksheets each

[Guest]

Hi,

I have data in a workbook that I need to split into different sheets.

I have a table that has (say) 500 records each for (say) colors Red, Green
and Yellow (totally 1500 records) in one table. This table needs to be split
into worksheets, and each sheet to have records only for each colour. I know
I can do a custom sort (for say records not equal to Red and Green) and
delete the non-relevant records, but I need to do this for very large tables.
Any help?

 

[Guest]

Why not filter on colours and cut/paste to relevant worksheets.

Less than 5 minutes work even for very large table as the filter will select
your records for copying..

 

[Gord Dibben]

Pradeep

How are you able to do a custom sort/filter on colored cells?

Are you using a UDF like Chip Pearson's SortbyColor function?

http://www.cpearson.com/excel/SortByColor.htm

Or do you mean the red, green, yellow is text in the cells?


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord,

Thanks for the SortbyColor function link, I was looking at something like
this for sometime.

I guess the example I used was misleading. I just the color as an example.
You are right - the Red, Green, Blue, Yellow etc. are text in the column on
the basis of which I need to split the whole table.

The problem is that I have a huge database which I need to split. Doing a
custom filter some 30-40 times is tedious, time consuming and there is a lot
of scope for error while copy-pasting.

Pradeep

 

[Guest]

Hi "Toppers",

I am doing exactly that right now - want to do something to simplify my work.

The problem is that I have a huge database which I need to split. Doing a
custom filter some 30-40 times (since I have 40 odd "Colors", so to speak,
for which I need to creat separate tables) is tedious, time consuming and
there is a lot of scope for error while copy-pasting.

Pradeep

 

[Frank Hayes]

I had to write a macro to do this just yesterday. If you know VBA at all,
you can modify the code to suit your needs. In this example, I was taking
the "Price Sheet" and splitting it into 4 regional worksheets. The 4
regional worksheets were already created. The region name was in column 7
of the Price Sheet.

Good luck

Sub WriteRegions()

' This macro was written by Frank Hayes on May 4, 2007

Application.ScreenUpdating = False

' See how many Rows and Columns are in the selected range
Set ws1 = ActiveWorkbook.Worksheets("Price Sheet")
ws1.Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
P1TotalRows = Selection.Rows.Count
P1TotalCols = Selection.Columns.Count

' Redim the array to match the selected range
ReDim P1Array(1 To P1TotalRows, 1 To P1TotalCols)
ReDim EU_Array(1 To P1TotalRows, 1 To P1TotalCols)
ReDim NA_Array(1 To P1TotalRows, 1 To P1TotalCols)
ReDim AP_Array(1 To P1TotalRows, 1 To P1TotalCols)
ReDim LA_Array(1 To P1TotalRows, 1 To P1TotalCols)

' Load the selected range into an array
ws1.Activate
ws1.Range("PriceData").Select
P1Array = Selection


' Redim the array to match the selected range
NewCols1 = P1TotalCols + 1

' Split the regions
ReDim TempArray(1 To P1TotalRows + 1, 1 To NewCols1)
i = 1
j = 1
k = 1
L = 1

' The column I want to split on is in column 7 in this workbook
SplitCol = 7

For X = 1 To UBound(P1Array)
If P1Array(X, SplitCol) = "REGION NA" Then
For Y = 1 To P1TotalCols
NA_Array(i, Y) = P1Array(X, Y)
Next
i = i + 1
ElseIf P1Array(X, SplitCol) = "EU" Then
For Y = 1 To P1TotalCols
EU_Array(j, Y) = P1Array(X, Y)
Next
j = j + 1
ElseIf P1Array(X, SplitCol) = "AP" Then
For Y = 1 To P1TotalCols
AP_Array(k, Y) = P1Array(X, Y)
Next
k = k + 1
ElseIf P1Array(X, SplitCol) = "LJ" Then
For Y = 1 To P1TotalCols
LA_Array(L, Y) = P1Array(X, Y)
Next
L = L + 1

End If

Next

Sheets("Europe").Range("A2").Resize(UBound(EU_Array), P1TotalCols) =
EU_Array
Sheets("North America").Range("A2").Resize(UBound(NA_Array),
P1TotalCols) = NA_Array
Sheets("Latin America").Range("A2").Resize(UBound(LA_Array),
P1TotalCols) = LA_Array
Sheets("Asia Pacific").Range("A2").Resize(UBound(AP_Array), P1TotalCols)
= AP_Array



' Finish Out
Application.ScreenUpdating = True
Application.StatusBar = " "
MsgBox "The regions have been split."


End Sub

 

[Gord Dibben]

How did we get from 3 sets of 500....red, green, yellow for 1500 records to 30
or 40 in a huge database?

It's always the "etc." that adds the wrinkles<g>

See Ron de Bruin's site for code to copy filtered items to a new sheet.

http://www.rondebruin.nl/copy5.htm


Gord

 

[Guest]

Hi Frank,

Frankly, I have no clue of VBA at all. I will ask some of my other techie
friends to help me out with modifying your code. Thanks for all the help.

Pradeep
----------------

 

[Guest]

Well, yes, that's true...the etc. is always the one that poses the problems.


Thanks for the link though, Gord. I am a complete non-techie person, so will
need to check about modifying the code to suit my needs with some of my other
not so technically challenged friends, but this helps. Let me work on it a
bit and see if I can come up with something useful.

Thanks for the help once again.

Pradeep