Sorting data query

**bmoro** · 11-11-2008, 06:28 PM

Hi all,

Here's what I am trying to do:

I am trying to create an item master (list of all the products the company makes). Our company has three factories and each make different products. I need to create a workbook that list all of the products details under the column names like "Item Code, Item Names, Pack Size, Factory".

I would like to be able to enter new products on Sheet1 then they will automatically appear on the list on Sheet2, Sheet3 or Sheet4 depending on what factory they were made in.

Sheet1: Item Master(Input Sheet)
Enter new products (and their details) on this sheet.

Sheet2: Factory A
Items that are made in FactoryA automatically appear on this list.

Sheet3: Factory B
Items that are made in FactoryB automatically appear on this list.

Sheet4: Factory C
Items that are made in FactoryC automatically appear on this list.

Is there anybody that can give me some ideas on how to do this - I bet it is very simple but I am just not thinking right.

Thanks,
Bmoro

**piyushdabomb** · 11-11-2008, 08:49 PM

Hi,

The request is very feasible and I can EVEN do it for you. I need a sample workbook with all the sheets labelled and sample data to work off of. Without that, I can't help you because I don't know what sort of data you're really expecting here.

**Paul** · 11-11-2008, 08:56 PM

Hi Bmoro,

Perhaps the attached workbook will get you on the right track. You can enter item data onto the Master sheet, then click the Update Records button and it will move all of the items to their respective factory sheet.

If you have questions, let me know.

Option Explicit

Private Sub cmd1_Click()
    Dim i As Long, j As Long, ws As Worksheet, ws2 As Worksheet
    Set ws = Sheets("Master")
    With ws
        For i = 2 To .Range("A65536").End(xlUp).Row
            Set ws2 = Sheets(.Cells(i, 4).Value)
            j = ws2.Range("A65536").End(xlUp).Row + 1
            ws.Range("A" & i).Resize(0, 6).Cut ws2.Range("A" & j)
        Next i
    End With
End Sub

**martindwilson** · 11-11-2008, 09:05 PM

using an array formula ,this just does products to factory but can be extended to include other info if you want

**bmoro** · 11-11-2008, 09:19 PM

Thanks Piyushdabomb,

I attached a sample of the spreadsheet I have created (hopefully I attached it right).

Let me know what you think can be done.
Cheers

**bmoro** · 11-11-2008, 09:22 PM

Thanks PJoaquin,

You have gone to a lot of trouble to create an example. Its exactly what I need but I was unsure how to use the button and command as I have only been using excel for a few months now and have not learnt about forms.

**Paul** · 11-11-2008, 09:39 PM

Do you need assistance with adding the code to your workbook and adjusting it to suit your data?

**bmoro** · 11-11-2008, 09:42 PM

Both lol - sorry

**Paul** · 11-11-2008, 09:55 PM

Here you go.. your worksheet with the updated code and a command button on the Master sheet. Let me know if this works for you...

Private Sub cmd1_Click()
Application.ScreenUpdating = False
    Dim i As Long, j As Long, ws As Worksheet, ws2 As Worksheet
    Set ws = Sheets("OFS Item Master")
    With ws
        For i = 5 To .Range("B65536").End(xlUp).Row
            Set ws2 = Sheets(.Cells(i, 11).Value & " Items")
            j = ws2.Range("B65536").End(xlUp).Row + 1
            ws.Range("B" & i).Resize(1, 10).Copy
            ws2.Range("B" & j).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Next i
    End With
    Application.CutCopyMode = False
    ws.Range("B5:K" & i).ClearContents
Application.ScreenUpdating = True
End Sub

Thread: Sorting data query

Thread Tools

Display

Sorting data query

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions