PasteSpecial to another workbook

[mdmackillop]

This works
[vba]
Sub Macro1()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = ActiveSheet.Range("E5").Resize(10)
Set Rng2 = Workbooks("Book1.xls").Sheets(1).Range("A1")
Rng1.Copy
Rng2.PasteSpecial Paste:=xlValues, Transpose:=True
End Sub

[/vba]
but not this
[vba]
Sub GetData()
Dim Tgt As Range, i As Long
Set Tgt = Workbooks("distribution_factors.xls").Sheets("Dist. Factors").Range("E11")
For i = 5 To Range("B5").End(xlDown).Row
Cells(i, 2).Resize(, 8).Copy
Tgt.PasteSpecial Paste:=xlValues, Transpose:=True
Next

End Sub

[/vba]
Runtime error 9 - Subscript out of range.
Any ideas?

[Bob Phillips]

Similar code works for me Malcolm. It must really be a wrong name.

[mdmackillop]

Thanks for that.
I can the pastespecial working in a trial workbook, but not using the OP's original. http://vbaexpress.com/forum/showthread.php?t=9527
It's a minor niggle only, activating the workbook, but it doesn't seem to like it otherwise.

[Bob Phillips]

Surely, it is because that worksheet is protected?

[mdmackillop]

I've tried protecting the sample sheet and workbook. Here's my test workbooks

[Norie]

mdmackillop

I've downloaded the attachment and I can't reproduce the error you mention.

Both subs error with a message saying that cell you are trying to change is protected.

[Bob Phillips]

If I remove the sheet protection, it works fine.

[mdmackillop]

Hi Norie,
The code has to be run from the Sample Distro workbook.

Bob,
As you'll have seen from the original post, removing protection is not really an option.

Thanks for the input, the workaround is straightforward, so I guess can live with it.