On May 4, 10:20 am, Scoops wrote:
On 4 May, 15:01, Frank wrote:





In the macro below, I am attempting to test the value of the contents
of an Column (F). Based on that value, the macro should copy the row
contents to a new workbook. I suspect I am attempting to use the
wrong command as I am a newbie.

Compile error is: Statement invalid outside Type block

Sub UPDATE_STATUS()

' Purpose of this section is to delete old data from resultant
worksheets

Sheets("Personal").Select
Rows("2:65535").Select
Selection.Delete Shift:=xlUp

Sheets("Corporate").Select
Rows("2:65535").Select
Selection.Delete Shift:=xlUp

Sheets("Disconnect").Select
Rows("2:65535").Select
Selection.Delete Shift:=xlUp

' Purpose of this section is copying of data to appropriate resultant
worksheets

Sheets("Master").Select

Range("F:F") As Range
If Value = "P" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Personal").Cells(Rows.Cou nt, 1).End(xlUp)(2)
End If

If Value = "C" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Corporate").Cells(Rows.Co unt, 1).End(xlUp)(2)
End If

If Value = "D" Then
Cells(Row, 1).Resize(1, 29).Copy
Destination:=Worksheets("Disconnect").Cells(Rows.C ount, 1).End(xlUp)
(2)
End If

ActiveWorkbook.Save

End Sub

Hi Frank

To test the range try:

Sub TestRange()
Dim cell As Range

For Each cell In Range("F:F")
Select Case cell
Case "F"
'DoThis
Case "P"
'DoThat
Case "C"
'DoTheOther
End Select
Next

End Sub

You might want to consider dynamically defining the actual range
you're testing - are you really filling 65k+ rows?

Regards

Steve- Hide quoted text -

- Show quoted text -

Steve, I dont really need to test the entire worksheet, but the end
can change and it may have a discontiguous data set, so I cannot
define the precise end.

I replaced that function. Seems to work properly. How about one more
bit of assistance? Seems my paste statement contains an object
defined error now. (it functioned previously). The goal is to paste
to the next available row.