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?
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?