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]

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

Excel doesn't like your last line. You don't need the sheet reference as calculation is at application level not worksheet / workbook level. Try this:

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

 

[MARK858]

Try...

Sub BalancePrice5_Click()
    Sheets("Pluto").Range("Pippo").Copy
    Sheets("Topolino").Range("L11").[COLOR="#FF0000"]PasteSpecial[/COLOR] Link:=True
    Sheets("Topolino").[COLOR="#FF0000"]Calculate[/COLOR]
End Sub

 

[MARK858]

Does actually look like it is one of those nasty ones where you do need to select the range so unfortunately try the code below rather than what I posted earlier (basically the same as Fishboy already posted except the Calculate part. Still guessing that you actually wanted to calculate the sheet and not reset the calculation)

Sub BalancePrice5_Click()
    Sheets("Pluto").Range("Pippo").Copy
    Sheets("Topolino").Activate
    Range("L11").Select
    ActiveSheet.Paste Link:=True
    Sheets("Topolino").Calculate
End Sub

or a bit nicer...

Sub BalancePrice5_Click()
    Sheets("Pluto").Range("Pippo").Copy
    Application.Goto Sheets("Topolino").Range("L11")
    ActiveSheet.Paste Link:=True
    Sheets("Topolino").Calculate
End Sub

 

[Fishboy]

Does actually look like it is one of those nasty ones where you do need to select the range

Yeah, I had initially tried something similar to your first post but "computer said no"

I must admit that I didn't read between the lines with the calculation thing though. I had assumed the OP just wants to make sure calculation at application level was set to automatic however after seeing your codes I suppose they could have wanted to "calculate now" as you have suggested.

 

[gnara74]

It seems that there's something wrong.

The macro stops at the first line...
Sub BalancePrice5_Click()
Sheets("Pluto").Range("Pippo").Copy
Application.Goto Sheets("Topolino").Range("L11")
ActiveSheet.Paste Link:=True
Sheets("Topolino").Calculate
End Sub

 

[gnara74]

Thanks... but it doesn't work...

I created the Name Pippo, with name manager and there's a vlookup function in order to find the cell I need.
I attached the value for your reference : "=VLOOKUP("blu";Pluto!$A$1:$O$6;13)"
Maybe it can help

Thanks

 

[Fishboy]

It seems that there's something wrong.

The macro stops at the first line...
Sub BalancePrice5_Click()
Sheets("Pluto").Range("Pippo").Copy
Application.Goto Sheets("Topolino").Range("L11")
ActiveSheet.Paste Link:=True
Sheets("Topolino").Calculate
End Sub

My version is definitely working in my test data:

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

 

[gnara74]

Thanks a lot Fishboy!
Did you also create the name with vlookup in name manager to define the range "Pippo"?
"=VLOOKUP("blu";Pluto!$A$1:$O$16;13)"

 

[Fishboy]

Thanks a lot Fishboy!
Did you also create the name with vlookup in name manager to define the range "Pippo"?
"=VLOOKUP("blu";Pluto!$A$1:$O$16;13)"

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?