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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monta Zengerle - 31 Jul 2006 03:12 GMT

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

Reply to this Message

Dave Peterson - 31 Jul 2006 12:29 GMT

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.

> This works through "ActiveCell.Offset(0, -8).Activate" but then doesn't.
>
[quoted text clipped - 31 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message