1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hyperlink to copy data from one tab to another

Discussion in 'VBA Macros' started by msharma864512, Sep 25, 2018.

  1. msharma864512

    msharma864512 Member

    Messages:
    128
    Hi,

    I want to create a funcationality/macro, which will enable me to put the Excel IDs from this tab to tab named "Data"

    If I click on any company, then it will automatically copy the excel company ID of the respective company and paste the same in tab named "data" in cell A2

    The moment the ID is copied to the data tab, a pop up will come to pull the Ids of the company we have clicked previously

    Ids of competitors will be pulled on the basis of sub-sector

    For e.g. if we have selected ABB Ltd. Whose sub-sector is Electrical Equipement, then it will pop up the names of all the companies within Electrical Eqipment Segment, and then we can select the companies whom we want to include in tab named "Data"

    Regards,

    Attached Files:

  2. Belleke

    Belleke Active Member

    Messages:
    472
    Something like this?

    Attached Files:

  3. msharma864512

    msharma864512 Member

    Messages:
    128
    Hi Belleke,

    It looks great, and thank you very much for ur help.

    However, it would be great if it has the functionality to filter the companies by sub-sector

    For. e.g, if i click on show form button, and then if i select any company then it should automatically filter all the companies which are of the same sub-sector of the company selected.

    For e.g. if I select the company name "5N Plus" then all the companies from chemicals sub-sector should show in the form, then from that list i can select any company i want.

    Additionally, although the one click button looks fine, but will it be possible to open the form by clicking on the companies within cell range "A2:A304".

    For e.g. if I click on A6, (which happens to be Electrical Equipment company) then the form should open with only Electrical Companies in the list.

    Regards,
    Manish
  4. Belleke

    Belleke Active Member

    Messages:
    472
    This one does what you asked for.
    Just click somewhere in the table.
    see attached.

    Attached Files:

  5. msharma864512

    msharma864512 Member

    Messages:
    128
    Belleke,

    This is great, but only problem here is, its not letting me scroll through the table.
    In particular I dont want to apply hyperlink to entire table, I was just looking to add hyperlink to company name only, and that too be with CTRL button.

    For e.g if i have to click on any company name, it should be possible only with CTRL button along with selecting the required company name.

    Also, is it possible to have this macro without a table, because the data set i m working on doesnt contain any table and it will not be possible for me to include the data as table.

    Also,
    it would be helpful if you could also guide me how can i apply the same macro in different spreadsheet, as the data set in that file be a bit different.
    Last edited by a moderator: Sep 28, 2018
  6. Belleke

    Belleke Active Member

    Messages:
    472
    Next version

    Attached Files:

  7. msharma864512

    msharma864512 Member

    Messages:
    128
    Th
    This is really great Belleke,

    I was just looking for one small edits,

    Right now, if click on clear data button, it delete all the data below row 2,

    so is it possible to just delete the data from the column where data has been copied, in this case, all the data to be deleted below cell A2, and it should not delete any other data
  8. Belleke

    Belleke Active Member

    Messages:
    472
    Version 4

    Attached Files:

  9. msharma864512

    msharma864512 Member

    Messages:
    128
    T
    Thanls Bellek,

    There is just one small update I am looking for.

    In the dashboard tab, if i am trying to add blank rows and columns, then the filters are not working on sub-sector.

    For e.g. if I start my data from Cell C17 (Company name heading)
    and then if i try clicking on any company, then ideally the form should give the company name of the relevant sub-sector only, but its not giving the detals, and it is showing the whole company list.

    It would be great if you could add the functionality to do the same
  10. Belleke

    Belleke Active Member

    Messages:
    472
    Can you post an example, where I can see what you want to accomplish?
  11. msharma864512

    msharma864512 Member

    Messages:
    128
    Thanks Belleke,

    I was able to do it myself
  12. msharma864512

    msharma864512 Member

    Messages:
    128

    Thank You Very much Belleke for your tremendous help.
  13. msharma864512

    msharma864512 Member

    Messages:
    128
    Belleke,

    I am facing just one issue,

    My companies list is pulled from some other sheet and there are multiple conditions to pull the list, For e.g. if I click on A&D buttons, then column C only reflects the A&D companies. Hence in this case, if I try typing Ctrt +Q it showing an error,
  14. msharma864512

    msharma864512 Member

    Messages:
    128
    here are the attachment

    Attached Files:

  15. Belleke

    Belleke Active Member

    Messages:
    472
    If I see the pictures,I guess there is an issue in the UserForm_Initialize code.
    An did you assign Crtl +q to your macro?
  16. msharma864512

    msharma864512 Member

    Messages:
    128
    B
    Belleke, yes i did assign CTRL + Q to macro, and its working fine in normal situation, but as and when i click on any my filter buttons, macro is not working
  17. Belleke

    Belleke Active Member

    Messages:
    472
    Did you went trough the UserForm_Initialize code?
    Can you post a relevant example without sensitive input?
    I can't help you with only pictures.
  18. msharma864512

    msharma864512 Member

    Messages:
    128
    Hi,

    I have attached the spreadsheet, when u use the dropdown in the yellow highlighted cell in dashboard tab, Form will open if u select all, but it wont work if u select, core, focus, or G360

    Attached Files:

  19. Belleke

    Belleke Active Member

    Messages:
    472
    Like that?

    Attached Files:

  20. msharma864512

    msharma864512 Member

    Messages:
    128
    Not Exactly, as there are multiple dropdowns in my main worksheet, and defining the name for each entities would be difficult, and it would also be difficult to write codes for each.

    There are close to 40 entries with 5 dropdowns.

    So, I need a different option
  21. msharma864512

    msharma864512 Member

    Messages:
    128
    Hi Belleke,

    Did u get a chance to look at this?

Share This Page