Thanks for your help I will fiddle around with it this week end

"Joel" wrote:

the sheet names can be iether of the following

Sheets(1) - the first sheet in order in the workbook. the 2nd sheet would
be sheets(2)

Sheets("Sheet1") - the sheet name in double quotes. can be "ALD"

sht = "ALD" - using a varible with the sheet name in double quotes.

sheets(sht) - no double quote



No sure why it crashed unless it has to do something with you changing sheet
names. You may not have any data in column A in the first sheet.


"Novice Lee" wrote:

If I change the Sheet name (sheet 2) to ALD Would I change Sheets(2) to ALD?

"Joel" wrote:

Sub MoveData()

'clear sheet 2 and copy header row
With Sheets(2)
Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=.Rows(1)
End With

'clear sheet 3 and copy header row
With Sheets(3)
Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=.Rows(1)
End With

Sh1RowCount = 2
Sh2RowCount = 2
Sh3RowCount = 2

With Sheets(1)
Do While .Range("A" & Sh1RowCount) < ""
If .Range("B" & Sh1RowCount) < "" Then
.Row(Sh1RowCount).Copy _
Destination:=Sheets(2).Row(Sh2RowCount)
Sh2RowCount = Sh2RowCount + 1
End If
If .Range("C" & Sh1RowCount) < "" Then
.Row(Sh1RowCount).Copy _
Destination:=Sheets(3).Row(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With

'Sort sheet 3
With Sheets(3)

LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = .Rows("2:" & LastRow)
SortRange.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("C2"), _
Order2:=xlAscending, _
Header:=xlNo
End With
End Sub