Selecting a range relative to ActiveCell, copy it to second Workshett within Workbook

This works through "ActiveCell.Offset(0, -8).Activate" but then doesn't.

Any help appreciated.


Sub SortforMember()

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("I1").Select
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("a2").Select


Do Until Cell = "Null"

Sheet1.Activate
ActiveCell.Offset(1, 0).Activate

If ActiveCell = "Yes" Then


ActiveCell.Offset(0, -8).Activate
Range(Cells(1, 1), End(x1ToRight)).Copy _
destination:=Worksheets("Sheet2").Range(Cells(0, 0), Cells(0, 9))



Sheet2.Activate
ActiveCell.Offset(1, 0).Activate

End If

If ActiveCell = "No" Then
Sheet2.Activate
ActiveCell.Offset(0, 0).Activate
End If


Loop


End Sub

x1ToRight is xltoright (you have a 1 (one) instead of an L (ell).

But you have some other problems, too.

You may want to describe what you're doing. There are better ways than
depending on .selects and .activates and the activecell.



Monta Zengerle wrote:
>
> This works through "ActiveCell.Offset(0, -8).Activate" but then doesn't.
>
> Any help appreciated.
>
> Sub SortforMember()
>
> Worksheets("Sheet1").Activate
> Worksheets("Sheet1").Range("I1").Select
> Worksheets("Sheet2").Activate
> Worksheets("Sheet2").Range("a2").Select
>
> Do Until Cell = "Null"
>
> Sheet1.Activate
> ActiveCell.Offset(1, 0).Activate
>
> If ActiveCell = "Yes" Then
>
>
> ActiveCell.Offset(0, -8).Activate
> Range(Cells(1, 1), End(x1ToRight)).Copy _
> destination:=Worksheets("Sheet2").Range(Cells(0, 0), Cells(0, 9))
>
>
>
> Sheet2.Activate
> ActiveCell.Offset(1, 0).Activate
>
> End If
>
> If ActiveCell = "No" Then
> Sheet2.Activate
> ActiveCell.Offset(0, 0).Activate
> End If
>
>
> Loop
>
> End Sub

--

Dave Peterson