How can I use a hyperlink to copy and paste a table?

[Demention]

Hi everyone,

Thanks for taking the time to look at my thread.

As the title says, I want to copy and paste a table when clicking on a hyperlink.
I have tried this:

Private Sub Worksheet_FollowHyperlink_Item1(ByVal Target As Hyperlink)
    If Target.TextToDisplay = "ITEM 1" Then
    Sheet1.Range("P11111111").Copy
    Sheet1.[L3].PasteSpecial
    End If
End Sub

Reason being I have multiple tables with different items, and "ITEM 1" is only 1 of about 450 in my master sheet. Each of these tables does include a picture, but the pic didn't copy when I tried this. I'll be using photos in my master sheet.

How I would like this to work:
Click the hyperlink in the table at the top of the sheet.
When clicked, the corresponding table for the item is copied to L3.

So in the larger tables with items, the item names will match those of the item table.
I tried to do more than the one, but it stopped working. I have no VBA skills as yet, maybe a very very very little bit.

If anyone can help me out on this one, I'd be very grateful.

Thanks in advance!

 

[Nebu]

Hi:

Find the attached.

Note :

1. I have renamed all the shapes to Item Description for the macro to understand which shape it has to copy and paste, make sure you do the same in your original workbook. Its a one time exercise.
2. I have hard coded the range "Sheet1.Range("L41:AH78")", you will have to adjust the range as per your need.
3. I also made an assumption tat all your tables will be standard size while coding.

Thanks

 

[Demention]

Hey Nebu

I don't know how you do it, but thanks so much for this.

I'll have to buy you a carton one day.

Thanks again!

 

[Demention]

Hi there,

I'm having an issue with the shapes/picture renaming and for the pics to actually copy or paste.

Added a pic to the item table and renamed the pic to the description. I then imported a table with that item, clicked the item but the pic doesn't copy along with the table. Have I missed something?

Thanks in advance

*edit: Tried adding a pic to the workbook from this thread and still the same problem.

 

[Nebu]

Hi:

I think you will have to loop it one more time. Here I have added Item 11 and have rerun the macro and it is working fine.

Thanks

 

[Demention]

Hi again Nebu

I gave this a go and it works really well. Thanks again for your help.

 

[Demention]

Hi there,

When clicking a hyperlink, it brings up the picture just fine, but when I click another link the new picture is placed on top of the one before without it being removed first. Is there a workaround for that?

Thanks

 

[Nebu]

Hi:

I am not sure. The file I uploaded is working just working fine at my end it is not pasting the picture on the top of the other.

Thanks

 

[Demention]

Found the problem. I changed it to paste to M6 instead but didn't change both pieces of code.

Thanks mate

 

[Demention]

Hi Nebu

I know this is slightly off topic but does relate to the code. Is there a way to center the image in a cell. I tried playing around with it but I can't get it to do anything.

I merged some cells in the item table to give me one big cell to try get it to work but yeah.

Sub aTest()
    CenterMe ActiveSheet.Shapes(1), Range("M5")
End Sub

Sub CenterMe(shp As Shape, overcells As Range)
    With overcells
        shp.Left = .Left + ((.Width - shp.Width) / 2)
        shp.Top = .Top + ((.Height - shp.Height) / 2)
    End With
End Sub

Tried this but it pulls the button on the far left and centers it haha.

Thanks for your time and patience.

 

[Nebu]

Hi:

Find the attached.

Thanks