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

 

[Fishboy]

Based on your initial post I simply named a cell somewhere on the Pluto sheet as Pippo. If you know the named range exists you don't need the VLOOKUP to find it.

Are you trying to make it so that a VLOOKUP is used to determine that the range you what is called Pippo?

Right, I've gone out on a limb here just in case you did want to use VLOOKUP to determine what named range is the one to be copied.

The amended macro to incorporate the result of a VLOOKUP as the desired named range to copy can be found below:

Sub BalancePrice3_Click()
' Defines variable
Dim Rng As String
' Sets value of Rng as the result of your VLOOKUP
    Rng = Application.Evaluate("=VLOOKUP(""blu"",Pluto!$A$1:$O$16,13,FALSE)")
' Copies the named range that matches the VLOOKUP result
        Range(Rng).Copy
' Pastes link to cell L11 of Topolino sheet
            Sheets("Topolino").Select
                Range("L11").Select
                    ActiveSheet.Paste Link:=True
' Ensures calculation is set to Automatic
        Application.Calculation = xlCalculationAutomatic
End Sub

I have uploaded a simplified example workbook here. I have taken the liberty of naming another cell as Fishboy. If you change the "Pippo" cell in the lookup table to Fishboy and press the macro button you will see that it actually looks up the value and works out which cell to copy.

PS - Your original VLOOKUP was missing the final argument of True or False BTW

 

[gnara74]

The problem is that the vlookup is mandatory because the cell I refer to, it's not always in the same position.
Would it be possible to include the vlookup in the macro and then copy and paste as link in the other worksheet?
Thanks a lot for your help

 

[gnara74]

GREAT!!!!
I will test it right now and let you know!
Cell called Fishboy it's better than the one called Pippo

 

[Fishboy]

The problem is that the vlookup is mandatory because the cell I refer to, it's not always in the same position.
Would it be possible to include the vlookup in the macro and then copy and paste as link in the other worksheet?
Thanks a lot for your help

See above

 

[gnara74]

Mmmm... no... there's something wrong.
I think it's easier of what you did.
The vlookup is necessary to find the cell in the 13th column (O) of a row, and I know the content of the first cell of this row (call it "blu")
Then, the cell found (that should be something like "O12" or "O13" or "O44" (every time the row changes), should be copy and paste as a link in another sheet.

 

[Fishboy]

Mmmm... no... there's something wrong.
I think it's easier of what you did.
The vlookup is necessary to find the cell in the 13th column (O) of a row, and I know the content of the first cell of this row (call it "blu")
Then, the cell found (that should be something like "O12" or "O13" or "O44" (every time the row changes), should be copy and paste as a link in another sheet.

O is actually the 15th column, but I assume the 13th column of the lookup table?

 

[gnara74]

Yes... because it's M but it doesn't matter which column, it's something that I can change easily
the problem is that I'm not able to implement the vlookup - copy the cell of the result and paste it as link

Thanks for being so patient!

 

[Fishboy]

Did you try out my test document?

 

[MARK858]

Then, the cell found (that should be something like "O12" or "O13" or "O44" (every time the row changes), should be copy and paste as a link in another sheet.

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

Did you try out my test document?

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

 

[gnara74]

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