Index and Hlookup (I think)

[Alteran]

I am looking for a way which I can take a main sheet of records with 5 different types and break them out into five sheets by type automatically as the data is populated in the main sheet

For example my types are CC, DRF, CP, DRC and DRCP

I have my Main sheet, and one sheet representing each type.

To move the information from the main sheet into the individual sheets I would like to use functions that I could just carry down that would pull the rows/records that match the data type, is there a simple way to accomplish this?

 

[MrKowz]

This can be done easily with a macro. What column would these categories appear in on your main sheet? Do you want to copy the entire row of each one over to their corresponding worksheet?

I would not reccomend using formulas to accomplish this, because that could potentially be far too many lookup-style formulas that will slow your sheet down.

 

[Alteran]

The Types are in column B of my Rollup Sheet. I am interested to see what would do this with VBA

 

[MrKowz]

In your Rollup sheet, what is the last column where data is entered that I can use to tell the macro when to copy over the line?

ie: if you enter data in columns a, b, c, d, e, f, and g, I would want the macro to fire after you change column G.

Also, to prevent possible duplicates, is there any unique value associated with each record that could be used to test if the record already exists?

 

[Alteran]

The last Colum is R

In column J I have a person's name (can be multiple rows with same name)
In Column E We have a ID however each ID can be used by multiple people, but each person can only use each ID once.

 

[MrKowz]

Place the following code in a module and run the GroupReport() macro once. This will do the initial placement for records you currently have entered.

Public Sub GroupReport()
Dim i   As Long, _
    sws As Worksheet, _
    sLR As Long, _
    dws As Worksheet, _
    dLR As Long
    
Set sws = ActiveSheet
sLR = sws.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To sLR
    If Not WorksheetExists(sws.Range("B" & i).Value) Then
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = sws.Range("B" & i).Value
    End If
    Set dws = Sheets(sws.Range("B" & i).Value)
    dLR = dws.Range("B" & Rows.Count).End(xlUp).Row + 1
    sws.Rows(i).Copy Destination:=dws.Range("A" & dLR)
Next i
End Sub

Function WorksheetExists(WSName As Variant) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Next, place this code in your Rollup Sheet's code (not in a module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("R:R")) Is Nothing Then
        Dim sws As Worksheet, _
            dws As Worksheet, _
            dLR As Long
            
        Set sws = ActiveSheet
        If Not WorksheetExists(sws.Range("B" & Target.Row)) Then
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = sws.Range("B" & Target.Row).Value
        End If
        Set dws = Sheets(sws.Range("B" & Target.Row).Value)
        dLR = dws.Range("B" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=dws.Range("A" & dLR)
    End If
End Sub

This way, whenever you edit column R, it will copy that row to the corresponding worksheet based on the value in column B.

This code also has the capability to automatically accomodate additional categories by creating a worksheet for them.

Hope this works for you!

 

[Alteran]

I am getting an error at this part of the code on both portions when I run it:

ActiveSheet.Name = sws.Range("B" & Target.Row).Value

 

[MrKowz]

What version of Excel do you use?

 

[Alteran]

I am using MS Excel 2003 11.8169.8172 SP3

 

[Alteran]

See if I could use a function I think it would yes be much slower however it would be more easily understandable for myself and others.