Results 1 to 2 of 2

Thread: Clone/Link cells and automatically prioritize/rank in different worksheet

  1. #1

    Post Clone/Link cells and automatically prioritize/rank in different worksheet

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys,
    Thank you so very much in advance for your help!

    I have a workbook where I have worksheet 1 with a list of several hundred firms prioritized and color coded from A-D

    A Car Company
    A Furniture Company
    A Food Company
    C Shoe Company
    B Shirt Company
    B Toy Company
    A Electronics Company

    Now I want to create worksheet 2 for my boss with the A-ranked companies at the top so that he knows which he should work on. It is important that if I add an A-company to worksheet 1 or change a B-company to an A-Company in worksheet 1, it automatically also shows up at the top in worksheet 2.

    So I think what I would basically need is to clone worksheet 1 cells to worksheet 2 and have them automatically rank in worksheet 2.

    It would be absolutely fantastic if someone could help me out! Again thank you so very much in advance! I really appreciate it.

    (On a quick side note: When I "just" link the cells between two worksheets, it doesn't copy all the attributes like drop down menu or color coding, is that right?)

    Best wishes!


  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Add this code to the Sheet1 code module

    Private Sub Worksheet_Change(ByVal Target As Range)
        With Worksheets("Sheet2")
            Me.Cells.Copy .Range("A1")
            .Cells.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
        End With
    End Sub

Posting Permissions

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