Copy data from worksheet to worksheet then sort

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Morning.
In my workbook i have two worksheets.
On my worksheet "HONDA SHEET" data is entered into certain cells.
These cells are arranged A-Z
Like so,
ACCORD ID48
CRV ID46
JAZZ ID8E
STREAM ID8E

In the other worksheet "INFO" the same data is shown but currently in order of smallest to largest so i can see which items are selling better than others,this is sorted from a macro button on "HONDA SHEET"
Like so,
JAZZ ID 8E 37
ACCORD ID48 20
CRV ID46 4
STREAM ID8E 1

This is my goal for today.
As data is entered into the cells on "HONDA SHEET" automatically update them on "INFO" sheet.

I will have a macro button on "HONDA SHEET" which will take me to the "INFO" sheet BUT show me the items from smallest to largest.

Please can you advise me some pointers.
Im lost as to how the cells are updated from one sheet to the other because each time i use the macro button the "INFO SHEET" cell data will be constantly moving there location and showing me the selling popularity of sold items.

Example
If cell AV4 is ACCORD ID48 but then i sell a few of them and now its new location is cell AV1, how does the code that updates the "INFO" sheet know to now update the ACCORD ID48 in cell AV1 and not AV4 ??

Where i mention macro button it will just be press button then take me to the page in question,the main bit is files be updated & arranged each time.
Code:
Sub LEADERBOARD()'
' LeaderBoard Macro
'


'
    Sheets("INFO").Select
    Range("AV1").Select
        ActiveWorkbook.Worksheets("INFO").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("INFO").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "AW1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("INFO").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With
End Sub
Please advise.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
We always need exact details:
You said:
"On my worksheet "HONDA SHEET" data is entered into certain cells."

Excel does not understand "Certain cells"

We need to know in what column will you be entering data.

Are you saying for example:

When I change a value in column "B" on sheet named "Honda" from lets say "4" to "5"

You want the script to search column "A" on sheet named "Info" For the name in column "A" adjacent to the changed value in column "B" on sheet named "Honda" and enter the new value now in column "B" on sheet named "Honda" and enter that value into column "B" on sheet named "info". Is this what you want?

If so and my columns are wrong please correct me.
 
Upvote 0
if you want data to update instantly, then you can use Worksheet_change function, located directly in worksheet honda. but i don`t recommend, because your rows are mixing all the time if i understand you right.

i suppose you have only 1 column to change values, and you have a unique ID, that you can track on sheet INFO and make changes to some of cells.

PS if you had 3 sheets:
1 for data input, no changes on that sheet
2 honda sheet
3. info
2 & 3 could be updated after any changes on sheet 1, and, may be, protected from changes
 
Upvote 0
Sorry but your reply didnt come through.
Then i put up links not knowing.

Data is entered into cells in these columns "I" and also "K"
They are entered using this code from another cell on the page,there is more code but just relates to which cell to update etc,if you need ALL of it then no problem just ask.

Code:
Target.Interior.ColorIndex = 6If Not Intersect(Target, Range("F17")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

The cells on HONDA SHEET then need to update "INFO" & then sort as shown in photo.
I would post the file but not allowed to post attachments.

This is HONDA SHEET
excel1.jpg


This is INFO

excel2.jpg
 
Upvote 0
I do not understand this:
Data is entered into cells in these columns "I" and also "K"
They are entered using this code from another cell on the page,there is more code but just relates to which cell to update etc,if you need ALL of it then no problem just ask.

This little snippet of code does not enter any values.


I need to see all the code you already have in your sheets

And you answered none of my questions
<strike></strike>
I do see your using column "I" and "K" and "AV and "AW"
 
Upvote 0
Ok sorry,

Here we go.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)With ThisWorkbook.Sheets("HONDA SHEET")
If Not Intersect(Target, .Range("A13")) Is Nothing And .Range("A13") <> "" Then
If Len(.Range("A13").Value) <> 17 Then
               .Range("A13").Interior.ColorIndex = 3
               MsgBox ("Honda Chassis Number Must Be 17 Characters, Please Try Again")
                .Range("A13").ClearContents
                .Range("A13").Interior.ColorIndex = 2
                .Range("A13").Activate
Else
                Application.EnableEvents = False
                .Rows(17).Insert Shift:=xlDown
                .Range("A17:G17").Borders.Weight = xlThin
                .Range("G17").Value = Date
                .Range("A17").Value = UCase(.Range("A13").Value)
                .Range("B17").Select
                .Range("A13").ClearContents
                Application.EnableEvents = True
End If
End If
End With


Target.Interior.ColorIndex = 6
If Not Intersect(Target, Range("F17")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "ACCORD ID 48" Then Range("D1").Value = Range("D1").Value + 1
If Target.Value = "ACCORD ID 8E" Then Range("D2").Value = Range("D2").Value + 1
If Target.Value = "BLACK NRK ID 46" Then Range("D3").Value = Range("D3").Value + 1
If Target.Value = "BLACK NRK ID 48" Then Range("D4").Value = Range("D4").Value + 1
If Target.Value = "BLACK NRK ID 8E" Then Range("D5").Value = Range("D5").Value + 1
If Target.Value = "CIVIC CE0523" Then Range("D6").Value = Range("D6").Value + 1
If Target.Value = "CRV HLIK-1T" Then Range("D7").Value = Range("D7").Value + 1
If Target.Value = "CRV ID 48" Then Range("D8").Value = Range("D8").Value + 1
If Target.Value = "FLIP REMOTE 2B" Then Range("D9").Value = Range("D9").Value + 1
If Target.Value = "FLIP REMOTE 3B" Then Range("D10").Value = Range("D10").Value + 1
If Target.Value = "FRV ID 48" Then Range("D11").Value = Range("D11").Value + 1
If Target.Value = "FRV ID 8E" Then Range("D12").Value = Range("D12").Value + 1
If Target.Value = "G8D-345H-A" Then Range("D13").Value = Range("D13").Value + 1
If Target.Value = "G8D-348H-A" Then Range("F1").Value = Range("F1").Value + 1
If Target.Value = "G8D-350H-A" Then Range("F2").Value = Range("F2").Value + 1
If Target.Value = "G8D-453H-A" Then Range("F3").Value = Range("F3").Value + 1
If Target.Value = "G8D-456H-A" Then Range("F4").Value = Range("F4").Value + 1
If Target.Value = "HON 58 ID 13" Then Range("F5").Value = Range("F5").Value + 1
If Target.Value = "HON 58 ID 48" Then Range("F6").Value = Range("F6").Value + 1
If Target.Value = "JAZZ HLIK-1T" Then Range("F7").Value = Range("F7").Value + 1
If Target.Value = "JAZZ ID 48" Then Range("F8").Value = Range("F8").Value + 1
If Target.Value = "JAZZ ID 8E" Then Range("F9").Value = Range("F9").Value + 1
If Target.Value = "LEGEND ID 8E" Then Range("F10").Value = Range("F10").Value + 1
If Target.Value = "SILVER NRK ID 48" Then Range("F11").Value = Range("F11").Value + 1
If Target.Value = "SILVER NRK ID 8E" Then Range("F12").Value = Range("F12").Value + 1
If Target.Value = "72147-S2H-G01" Then Range("F13").Value = Range("F13").Value + 1
End If
    If Target.Address = "$F$17" Then
        Call sheettolist
    End If
End Sub
 
Upvote 0
My answer is this, is the supplied code now ok for you.

Thanks
 
Upvote 0
I'm sorry but I do not believe I can help you this is beyond my knowlege base. I'm sure someone else here at Mr. Excel will be able to help you. I will continue to monitor this thread.
My answer is this, is the supplied code now ok for you.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top