Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Copy data from worksheet to worksheet then sort

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    833
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy data from worksheet to worksheet then sort

    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.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,858
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    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

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Posts
    833
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort


  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,858
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    I never click on links. If your not willing to answer my questions then I'm not able to help you.
    Quote Originally Posted by ipbr21054 View Post
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    833
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    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


    This is INFO


  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,858
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    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

    I do see your using column "I" and "K" and "AV and "AW"
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Nov 2010
    Posts
    833
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    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

  9. #9
    Board Regular
    Join Date
    Nov 2010
    Posts
    833
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    My answer is this, is the supplied code now ok for you.

    Thanks

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,858
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy data from worksheet to worksheet then sort

    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.
    Quote Originally Posted by ipbr21054 View Post
    My answer is this, is the supplied code now ok for you.

    Thanks
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com