Copy and paste special in vba

[gnara74]

Hi All,
I'm trying to create a macro that should do the following things:
1. Look for a cell named "Pippo" (named through Name Manager) in the worksheet called "Pluto" or through the application vlookup
2. Copy the cell found (named Pippo) and paste it as link in the cell L11 of the sheet named "Topolino".

I'm pretty new with VBA and I created this macro but it doesn't work.

Sub BalancePrice3_Click()


Sheets("Pluto").Select
ActiveSheet.Range("Pippo").Select
Selection.Copy
Sheets("Topolino").Select
Range("L11").Select
ActiveSheet.Paste Link:=True
Sheets("Topolino).Application.Calculation = xlCalculationAutomatic

End Sub




Can anybody help me?
Would it be easier use VLookup in VBA?

It's pretty urgent!

Thanks

 

[gnara74]

gnara74, as well as the question below from Fishboy are you expecting the code to run when the result in your Vlookup changes?


This thread has grown a lot larger than I expected it to when I last looked.

No, it should simply identify the cell - copy and paste it as a link.
Is there anything that I could do in order to explain it better?

 

[Fishboy]

Yes, but it's not what I need. Because it's making reference to a 3rd cell that I do not need.

No, it should simply identify the cell - copy and paste it as a link.
Is there anything that I could do in order to explain it better?

So, the 3 cells it references are:

The one which contains "Blu"
The lookup result of "Pippo"
A named range cell called "Pippo"

Which of these 3 cells is superfluous to requirements? If you take any one of these cells out of the loop it will fall over.

It needs "Blu" to determine what the lookup result is.
The cell containing "Pippo" is the lookup result. Without this how will Excel know which cell you want to copy.
The cell called "Pippo" is the cell you want to copy.

I am confused...

 

[MARK858]

I am confused...

That had me worried I thought it was just me

 

[gnara74]

Sorry guys for being so "dummy".

The one which contains "Blu" - needed - this is the value needed for the lookup
The lookup result of "Pippo" - needed - this is the value I need to copy
A named range cell called "Pippo" - not needed.
Forget about giving a name to the range, because it's a step I can avoid.

Thanks

 

[MARK858]

So just to be clear...

1) find text "Blu" in column A
2) find the cell in the 13th column of the same row as the text "Blu" was found.
3) link Sheets("Topolino").Range("L11") to the cell in 2) and copy the value.

Have I missed/ got anything wrong (please note there is nothing so far I need a Vlookup for)?

 

[gnara74]

The Vlookup was the easiest way to find the column 13 in the row with Blu.
And this is the cell to be linked to the Sheets("Topolino").Range("L11").
It means that in the Sheet Topolino (destination) will appear only a link to the cell in the column 13 of the Sheet Pluto.

I hope it's more clear now

 

[MARK858]

Try either...

Sub BalancePriceXX_Click()
    Dim x As String
    x = Sheets("Pluto").Columns("A:A").Find(What:="Blu", _
                                            After:=Cells(1, 1), LookIn:=xlValues, _
                                            LookAt:=xlWhole, SearchOrder:=xlByRows).Offset(, 12).Address
    Sheets("Topolino").Range("L11").Formula = "=Pluto!" & x
    Sheets("Topolino").Calculate
End Sub

or

Sub BalancePriceX_Click()

Dim Rng As Range, x As String

Set Rng = Sheets("Pluto").Columns("A:A").Find(What:="Blu", _
                                            After:=Cells(1, 1), LookIn:=xlValues, _
                                            LookAt:=xlWhole, SearchOrder:=xlByRows).Offset(, 12)
x = Rng.Parent.Name & "!" & Rng.Address


        Range(x).Copy
            Sheets("Topolino").Select
                Range("L11").Select
                    ActiveSheet.Paste Link:=True

  Sheets("Topolino").Calculate
End Sub

I don't really see the need for a Calculate but I have left it in.