Taking rows containing specific text and moving to specific tab.

[X82]

Our document contains around 40K entries of various geographical locations. Each of these locations belong to one of 10 major areas. Currently we have a tab for each of these 10 major areas. We have to (each month) look through the first column and match up with of the 64 locations to decide which major area it falls under. We cut those rows and have to paste them into the correct tab which is named after the major area (10 major areas, 64+ sub areas).
In my head it looks simple to automate, if cell A1 contains Cumbria, select the entire row and move it into the major area which looks after Cumbria.

Below is an example, since I am at work and unable to upload any examples or post any screenshots.

So, 10 tabs named Jan to Dec, if any cell in the first column contains Alpha or Bravo, move the entire row into tab Jan.
If any cell in the first column contains Charlie or Delta, move the entire row into the Feb tab.

Repeat for the entire list.

There may be some issues as some entries are all uppercase or lower, so I need to capture both. If it fails to find any match, then leave the data as is.

I have created my spreadsheet with 3 tabs so far.
Data - Containing over 40K rows of information. The vital information is in the first column, sub areas)
Locatoons - This tab contains a list in the first column of the 10 major areas (alpha, Bravo etc). To the right (columns B onwards) are the corresponding sub areas for.

Example:

[TABLE="width: 1141"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Alpha[/TD]
[TD]AVON[/TD]
[TD]BRISTOL[/TD]
[TD]CORNWALL[/TD]
[TD]DEVON[/TD]
[TD]DORSET[/TD]
[TD]GLOUCESTERSHIRE[/TD]
[TD]SOMERSET[/TD]
[TD]WILTSHIRE. [/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD]COUNTY DURHAM[/TD]
[TD]DURHAM[/TD]
[TD]NORTHUMBERLAND[/TD]
[TD]YORKSHIRE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]BEDFORDSHIRE[/TD]
[TD]CAMBRIDGESHIRE[/TD]
[TD]DERBYSHIRE[/TD]
[TD]ESSEX[/TD]
[TD]HERTFORDSHIRE[/TD]
[TD]LEICESTERSHIRE[/TD]
[TD]LINCOLNSHIRE[/TD]
[TD]NORTHAMPTONSHIRE[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]BERKSHIRE[/TD]
[TD]BUCKINGHAMSHIRE[/TD]
[TD]HAMPSHIRE[/TD]
[TD]KENT[/TD]
[TD]OXFORDSHIRE[/TD]
[TD]SUSSEX[/TD]
[TD]SURREY[/TD]
[TD]ISLE OF WIGHT[/TD]
[/TR]
</tbody>[/TABLE]

Ideally I would create 10 tabs (Alpha, Bravo, etc) and each row containing any sub area would to into that tab. So Essex into Charlie tab, cornwall into Alpha and so forth

Can anyone help me with this mission? We currently have to do this manually and the staff are ready to strike!

 

[bugmonsta]

Hi there,

Many moons ago I found a bit of code on another site that will copy data into new tabs which I think will help you out.

Visit Contextures via Contextures - ExcelFiles and search the page for file FL0013.

This is a zip file containing an example set of data that the code steps through and copies out.

As I understand that people (including me) are dubious with opening links/files, below is a copy of the code.

The table below is a quick copy of some of the data - and the code will create new tabs on column C (Rep) and copy all the rows for this rep into the new sheet (add more to test - or change to match your needs!) So in the example below you would end up with tabs called Sheet1, Gill and Anderson.

The process is done by using columns J and L to work the filters out, so please test this on data that does not include these columns first!

Date	Region	Rep	Item	units	Cost	Total
1/2/12	Ontario	Anderson	Clipboards	20	4.99	99.80
1/2/12	Ontario	Gill	Binders	8	19.99	100

Option Explicit

' Developed by Contextures Inc.
' www.contextures.com

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Dim bAF As Boolean
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")
bAF = ws1.AutoFilterMode

'extract a list of Sales Reps
With ws1
    .Columns("C:C").Copy _
      Destination:=.Range("L1")
    .Columns("L:L").AdvancedFilter _
      Action:=xlFilterCopy, _
      CopyToRange:=.Range("J1"), Unique:=True
    r = .Cells(Rows.Count, "J").End(xlUp).Row
    .Columns("L:L").ClearContents
    
    'set up Criteria Area
    .Range("L1").Value = .Range("C1").Value

    For Each c In .Range("J2:J" & r)
    
      'add the rep name to the criteria area
      .Range("L2").Value = _
            "=""="" & " & Chr(34) & c.Value & Chr(34)
      
      'add new sheet (if required)
      'and run advanced filter
      If WksExists(c.Value) Then
        Sheets(c.Value).Cells.Clear
        rng.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("L1:L2"), _
            CopyToRange:=Sheets(c.Value).Range("A1"), _
            Unique:=False
      Else
        Set wsNew = Sheets.Add
        wsNew.Move After:=Worksheets(Worksheets.Count)
        wsNew.Name = c.Value
        rng.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("L1:L2"), _
            CopyToRange:=wsNew.Range("A1"), _
            Unique:=False
      End If
    Next
    
    .Select
    .Columns("J:L").ClearContents
    
    If bAF = True Then
        .Range("A1").AutoFilter
    End If

End With
End Sub
Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

 

[X82]

wow thank you. That looks like it could solve my problem.
I tried the code however and it errored on the line:

Set rng = Range("Database")

Method Range of object _Global failed

 

[bugmonsta]

Yeah, that would cause a problem, in the test file they have created the named range to be A1 to G43 (all the test data) - and I guess they did this as it means the code will only look at this named range and not all columns as they use column J and L for the filters.

If you are testing this then create a named range (formulas tab/name manager) and edit the code and move the filters to new columns (or try moving them to a new sheet?)

If your struggling then post back how many columns you have and I will see if I have time to edit the code for you.