mdmackillop
02-22-2007, 07:05 AM
I tried to set a dynamic range on another sheet from within a worksheet module as in DoCopies2 below, but I receive an error (1004 Method Range of Object Worksheet failed). DoCopies1 works and DoCopies2 works if I move it to a Standard module. Anyone know the reason for this? (original thread (http://vbaexpress.com/forum/showthread.php?t=11582))
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then DoCopies2
End Sub
Sub DoCopies1()
Dim cel as range
With Sheets("Sheet to Copy to")
.Range("A2:A100").ClearContents
For Each cel In Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(cel) = cel.Offset(, -1)
Next
End With
End Sub
Sub DoCopies2()
Dim cel as range
With Sheets("Sheet to Copy to")
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).ClearContents
For Each cel In Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(cel) = cel.Offset(, -1)
Next
End With
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then DoCopies2
End Sub
Sub DoCopies1()
Dim cel as range
With Sheets("Sheet to Copy to")
.Range("A2:A100").ClearContents
For Each cel In Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(cel) = cel.Offset(, -1)
Next
End With
End Sub
Sub DoCopies2()
Dim cel as range
With Sheets("Sheet to Copy to")
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).ClearContents
For Each cel In Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(cel) = cel.Offset(, -1)
Next
End With
End Sub