Solved: Setting a range from a Worksheet module

[mdmackillop]

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

[Norie]

Aren't you missing a dot qualifier here?

Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).ClearContents Or is that not the line of code giving the error?

[Bob Phillips]

As Norie says, you are missing a dot before the Range. That means the codes assumes that Range applies to Me, the containing worksheet, but the Cells property within that Range is point at 'Sheet to Copy to', a real bit of tortology. It is because of that that you (fortunately) get an error, not just assuming the activesheet.

[mdmackillop]

Thanks both.