Data base- arrange by brand

[•Daniel•]

Hi,

I have a data base of 8200 products, each product has a code of 6 to 7 characters, the 3 first characters tell me the brand of the product.
I will like my database separated by brand of product, hopefully a sheet per brand. Each product have 4 columns : code, description, und, price.

can a macro do that? pick the 3 first characters of the code, then take all the products that the code star with the 3 same characters and put them in a new sheet and do the same for the other like 15 brands. I can do it manually by copy and paste, its doesn't take a lot of time, but if i make a change in the database then...

Thanks....

 

[jbeaucaire]

1) Create a sheet called Brands. In column A just list the brands that will require a sheet of their own. Anytime you add a new brand, just add it to the list on this sheet. (this is the 3-digit codes that will be used to create sheets if they do not exist already)

2) Create a named range called BRANDS with the following formula as the
Source: =OFFSET(Brands!$A$1,0,0,COUNTA(Brands!$A:$A),1)

This will allow us to use the word "Brands" in the macro and it will always use all the brands you have listed in column A on the sheet BRANDS.

3) Name your main sheet "Database" or change the following macro

Option Explicit

Sub ParseDataToSheets()
'JBeaucaire (7/17/2009)
'Data in database sheet is copied to individual sheets by brand name autofilter column A
Dim ds As Worksheet, ws As Worksheet, v
Dim Rng As Range, Brands As Range, cell As Range

Application.ScreenUpdating = False
Set ds = Sheets("Database")
ds.Activate
Set Rng = Range("A2:D" & Range("A" & Rows.Count).End(xlUp).Row)
Set Brands = Range("Brands")
Range("A1").AutoFilter

    For Each cell In Brands
        v = Evaluate("ISREF(" & cell.Text & "!A1)")
        If Not v Then           'If sheet doesn't exist, create it
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = cell.Text
            ds.Range("A1:D1").Copy Range("A1")
            ds.Activate
        End If
            
        'Clear old data and add new data from database
           Sheets(cell.Text).Range("A2", "D" & Rows.Count).Clear
           Range("A1").AutoFilter Field:=1, Criteria1:=cell.Text & "*"
           Rng.Copy
           Sheets(cell.Text).Range("A2").PasteSpecial xlPasteValues
    Next cell
    
    For Each ws In Worksheets
        If ws.Name <> "Database" And ws.Name <> "Brands" Then
            ws.Activate
            Range("A1").Select
            Application.CutCopyMode = False
            Range("A2:D" & Rows.Count).Sort Key1:=Range("A1"), Order1:=xlAscending, _
                        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                        Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
        End If
    Next ws

ds.Activate
Range("A1").AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("A1").Select
End Sub

As indicated above, this will create the 3-digit coded sheets if they do not exist. Each time you run the macro it will completely reparse the data, the old data is deleted and new put in its place.

 

[•Daniel•]

hey thanks a lot, but i think i'm doing something wrong, the macro creates successful 24 sheets, each with their corresponding products, but, there are 79 brand in total, the other resting ones don't appear.

Maybe is because i dont know exactly what you mean with named range, what i did was: i put the formula in a cell and then i named the cell brands. Sryy

Thaks any way, great help!

 

[•Daniel•]

Hey i just found the mistake, first i created the range in 'administrador de nombres(im spanish so i dont know how's in eanglish)' not in a cell, then the macro didn't created the resting sheets becase in one code i put an extra character an space !

now all brands have their corresponding sheet! the only thing i must say is every sheet star with the first same code of the database, that dosen't correspond to the sheet brand.

i wanna learn macros now

Thanks your great,

 

[•Daniel•]

Of course, i forgot the title, my products started at A1 of database, i just inserted a row and typed the titles, lol.

bye thanks again

 

[jbeaucaire]

Good job Daniel. Well done.

Keep that in mind for the future. AUTOFILTER is an awesome and useful tool, both when used manually and when used in a macro to avoid having to "loop". But AUTOFILTER always requires titles at the top of the data.