automatic sort & update of sheet 1 data into Sheet 2

[gazzali]

Hi I am currently managing customer data in an excel 2000 work sheet. What I want is "Automatic update of datas in a particular row (Sheet 1) to another work sheet (Sheet 2) according to Customer ID Number IN A REQUIRED FORMAT. For Example

Date Customer-Name Product-Purchased Qty Amount Customer-ID

8/11/05 ABC & CO INC Canon PRNT 6600L 3 $785.00 #1001

The above information will be in Sheet 1. As I enter the Customer-ID #1001 it should automatically update in Sheet 2 as follus below:-

Date Customer-Name Product-Purchased Qty Amount

Customer-ID: #1001
12/8/05 ABC & CO INC HP Laser Cartridge 100 $585.65
8/11/05 ABC & CO INC Canon PRNT 6600L 3 $785.00

Hope it explains well. Anybody can help me how to get this thing automatically in excel 2000

 

[al_b_cnu]

Hi,

Welcome to the Board!

Try this code.

Insert the following as a Sheet2 Activate event, so it will only fire when you select sheet2:

Option Explicit

Private Sub Worksheet_Activate()
Dim iPtr As Integer
Dim lRow As Long, lRow1 As Long
Dim R As Range, rData As Range
Dim sPrev As String, sCur As String
Dim vData(1 To 5) As Variant
Dim WS As Worksheet

'** Sort Sheet1 **
Set WS = Sheets("Sheet1")
WS.Columns("A:F").Sort Key1:=WS.Range("F2"), Order1:=xlAscending, _
                        Key2:=WS.Range("A2"), Order2:=xlAscending, _
                        Key3:=WS.Range("C2"), Order3:=xlAscending, _
                        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
                        DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

'** Clear Sheet & store headings **
Cells.Clear
'Date Customer-Name Product-Purchased Qty Amount
vData(1) = "Date"
vData(2) = "Customer-Name"
vData(3) = "Product-Purchased"
vData(4) = "Qty"
vData(5) = "Amount"
lRow = 1
ActiveSheet.Range("A" & lRow & ":E" & lRow).Value = vData

'** Loop thru & populate sheet2 **
sPrev = ""
For Each R In WS.Range("F2:F" & WS.Cells(Rows.Count, "F").End(xlUp).Row)
    sCur = R.Text
    If sCur <> "" Then
        If sCur <> sPrev Then
            lRow = lRow + 2
            With ActiveSheet.Range("A" & lRow)
                .Font.Underline = xlUnderlineStyleSingle
                .Font.FontStyle = "Bold"
                .Value = "Customer-ID: #" & sCur
            End With
            sPrev = sCur
        End If
        lRow = lRow + 1
        lRow1 = R.Row
        For iPtr = 1 To 5
            vData(iPtr) = WS.Cells(lRow1, iPtr).Value
        Next iPtr
        ActiveSheet.Range("A" & lRow & ":E" & lRow).Value = vData
    End If
Next R
End Sub

To install,

Select sheet2, right-click the sheet tab & select 'View Code'.

Paste the above into the code window.

 

[gazzali]

Hi Alan thanks for your immediate reply.

However the VB Editor prompts "Compile Error: Variable not Defined" when I click Sheet 2 after entering the data in Sheet 1. by highlighting the word xlSortNormal. Its in Para 2 line 6 ( Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _)under '** Sort Sheet 1 **

'** Sort Sheet1 **
Set WS = Sheets("Sheet1")
WS.Columns("A:F").Sort Key1:=WS.Range("F2"), Order1:=xlAscending, _
Key2:=WS.Range("A2"), Order2:=xlAscending, _
Key3:=WS.Range("C2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, DataOption3:=xlSortNormal


And an Yellow Arrow highlighting "Private Sub Worksheet_Activate()" appears. And when I close it promps a message "The Command will close the debugger".

So what should I do now?

 

[al_b_cnu]

Hi Gazzali,

I think this is beacause I developed this on Excel 2003 not v2000.
Try

WS.Columns("A:F").Sort Key1:=WS.Range("F2"), Order1:=xlAscending, _ 
                        Key2:=WS.Range("A2"), Order2:=xlAscending, _ 
                        Key3:=WS.Range("C2"), Order3:=xlAscending, _ 
                        Header:=xlYes

 

[al_b_cnu]

Hi gazzali,

I tested this on Excel 2003, not v2000. Try:

WS.Columns("A:F").Sort Key1:=WS.Range("F2"), Order1:=xlAscending, _
Key2:=WS.Range("A2"), Order2:=xlAscending, _
Key3:=WS.Range("C2"), Order3:=xlAscending, _
Header:=xlYes