PDA

View Full Version : PasteSpecial to another workbook



mdmackillop
09-17-2006, 07:11 AM
This works

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


but not this

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


Runtime error 9 - Subscript out of range.
Any ideas?

Bob Phillips
09-17-2006, 08:15 AM
Similar code works for me Malcolm. It must really be a wrong name.

mdmackillop
09-17-2006, 09:02 AM
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
09-17-2006, 10:45 AM
Surely, it is because that worksheet is protected?

mdmackillop
09-17-2006, 11:05 AM
I've tried protecting the sample sheet and workbook. Here's my test workbooks

Norie
09-17-2006, 11:30 AM
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
09-17-2006, 11:36 AM
If I remove the sheet protection, it works fine.

mdmackillop
09-17-2006, 01:13 PM
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.