Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2016
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question Cell Referencing Help

    I'm trying to something similar to the OP in this thread, however, I need the cells to automatically update with the correct formatting.

    I'm trying to reference cells in sheet 2 to cells in sheet 1, however, it's not using the same format as the cells in sheet 1. I'm getting the same text but not the correct text color or any hyperlinks.

    Ex: In sheet 1 I named A141141 EdgePoint_Capital_Advisors.


    In Sheet 2 I copied =EdgePoint_Capital_Advisors into the cells A88. For some reason it's not giving me the same orange color and not including the hyperlink.






    I was able to copy the formatting using the following code:

    Private Sub Worksheet_Activate()
    Sheets("General").Range("EdgePoint").Copy
    Range("_216_831_2430").PasteSpecial Paste:=xlPasteFormats
    Sheets("General").Range("Access_Capital").Copy
    Range("_314_783_9550").PasteSpecial Paste:=xlPasteFormats
    End Sub

    _216_831_2430 and _314_783_9550 are just random cell names. This just copies the formatting though. How would I edit this to also copy the text?

    Also, when I sort bunch of cells (including the ones with the referencing) alphabetically my formatting gets all mixed up. Instead of applying the formatting to the intended cell(s) it applies them to different ones. How do I sort the cells and keep their cell names and values?


    Suggestions are appreciated!

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Jack,

    Try the following code:

    Code:
    Private Sub Worksheet_Activate()
     Sheets("General").Range("EdgePoint_Capital_Advisors").Copy _
                        Destination:=Range("_216_831_2430")
     End Sub

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    jack11101 (2016-07-11)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Jack,

    The above will copy the text and the formatting. Apply the same technique with the other ranges you want to copy.

    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    jack11101 (2016-07-11)

  6. #4
    New Lounger
    Join Date
    Jul 2016
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Amazing. Thank you so much!

    One quick question. When I sort the cells by alphabetical order it messes up the formatting (formatting stays behind in the original cell location). Is this avoidable?

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    jack,

    Thanks for the thanks. The formatting should follow the cell except for things like borders and conditional formatting. Can you post a sample ready for sorting so we can see the behavior in action.

    Maud

  8. #6
    New Lounger
    Join Date
    Jul 2016
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    jack,

    Thanks for the thanks. The formatting should follow the cell except for things like borders and conditional formatting. Can you post a sample ready for sorting so we can see the behavior in action.

    Maud
    For example, if I name A62 with the text 'Test6' as 'Pagani' and then sort cells A57:A63 alphabetically from "highest to lowest" cell A62 is still named as 'Pagani' despite 'Test6' now being in cell A58.

    Untitled.pngUntitled1.png

    I would like the cell name to follow the text when I sort it alphabetically.

Posting Permissions

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