vba paste special lnks

[Kaps_mr2]

Hi,

I am trying to write some VBA to paste links between workbooks. However, I am stumped:-

questionnaire.Sheets("section " & cell_txt1).Range("d" & next_blank_line).Copy
        questionnaire.Sheets("Summary").Range("c" & summary_sheet_next_blank_line).PasteSpecial , link:=True

what am I doing wrong ? thanks

Kaps

 

[MrKowz]

Pasting a link is actually not a part of the PasteSpecial method; it is a part of the Paste method (I know, it is really counterintuitive. I had to record a macro to figure it out)

Try:

questionnaire.Sheets("section " & cell_txt1).Range("d" & next_blank_line).Copy
        questionnaire.Sheets("Summary").Range("c" & summary_sheet_next_blank_line).Paste , Link:=True

 

[Kaps_mr2]

thanks - tried that and it gives me "object does not support this property or method".

 

[MrKowz]

Do you have "questionnaire" dimmed properly (as Workbook) and Set properly?

Also, looking a bit closer at my recorded code, we don't want the comma between paste and Link:=

questionnaire.Sheets("section " & cell_txt1).Range("d" & next_blank_line).Copy
        questionnaire.Sheets("Summary").Range("c" & summary_sheet_next_blank_line).Paste Link:=True

 

[xenou]

I was wondering what questionaire is too...

But (if I'm not mistaken) Paste is a method of a worksheet, not a range. It may be that in this case you have to activate the sheet, select the range, then paste.

Sheet1.Cells(1, 1).Copy
With Sheet2
    .Activate
    .Cells(1, 1).Activate
    .Paste , True
End With

 

[MrKowz]

I was wondering what questionaire is too...

But (if I'm not mistaken) Paste is a method of a worksheet, not a range. It may be that in this case you have to activate the sheet, select the range, then paste.

Sheet1.Cells(1, 1).Copy
With Sheet2
    .Activate
    .Cells(1, 1).Activate
    .Paste , True
End With

Range is a valid object for the paste method (according to the 2007 help files)

 

[xenou]

Hmmm,
are you sure?

Not listed here:
http://msdn.microsoft.com/en-us/library/bb245315(v=office.12).aspx

Did your code work for you?
ξ

 

[MrKowz]

Hmmm,
are you sure?

Not listed here:
http://msdn.microsoft.com/en-us/library/bb245315(v=office.12).aspx

Did your code work for you?
ξ

You are right, my apologies (misread the help file).

 

[xenou]

I did discover a new method for the range object as I was looking for paste ... the parse method. Never heard of it. Just posted in the lounge to ask if anyone ever uses it. So it was a useful mistake to me.

 

[Kaps_mr2]

questionnaire is defined as a workbook and is declared via set.

Have removed the "," - which I did not spot initially. Tried :-

 With questionnaire.Sheets("Summary")
        .Activate
        .Range("c" & summary_sheet_next_blank_line).Activate
        .Paste
        End With

which executes - but with a reference error in the destination cell. Putting "true" after the Paste results in an error - with excel saying thatit can't paste the data. Thanks

Regards

Kaps