Data sorting to a new sheet

[Novice Lee]

I'm not even sure this can be done. What am trying to do is sort data from
Sheet 1 and place the sorted data on sheet 2 & 3.
Here is what the data would look like:

1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO
2 INIT_SD 5.5.001
3 NAC_110 V2-01
A4-03
4 INIT_SD 5.5.021
5 INIT_SD 5.6.001
6 NAC_110 V3-01
A1-23
7 NAC_110 V1-01
A1-01

What I need is all Rows with something in Address1 column goes to sheet1 (in
order) and all Rows with something in column Strobe_circuit_info goes to
sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3).
ex.
3 NAC_110 V1-01
A1-01
30 NAC_110 V2-01
A4-03
57 NAC_110 V3-01
A1-23

so if the if there is a device V1-02 it would go in row 4 and so on, same
for sheet 2

Is this possible or am i really reaching

Thanks for your input

 

[Joel]

This doesn't sound too difficult. I just confused by the sheet numbers in
your description

1) what sheet is th original data located on?
2) I think you incorrectly said the data goes on sheet 1 instead of sheet 2.
3) You said to put the data inorder on sheet 1, but then said to sort data.
Which is correct?
4) Are yuor columns A toc D?

 

[Novice Lee]

Morning

In regards to your questions Joel:
1)the original data is imported to sheet 1
2)you are correct it should be sheet 2
3)both, the data needs to be in order on the sheets 2 & 3 but it also needs
to be put in groups.
4) there are more than four columns, but column b and column c are the main
sorting column. Every Init device will have something in the Address1 column
and every Nac device will have something in the Strobe_Circuit_Info Column.

A B C
D
1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO
2 INIT_SD 5.5.001
3 NAC_110 V2-01 A4-03
4 INIT_SD 5.5.021
5 INIT_SD 5.6.001
6 NAC_110 V3-01 A1-23
7 NAC_110 V1-01 A1-01


this is what it was suppose to look like

Thanks

 

[Joel]

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

 

[Novice Lee]

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

 

[Novice Lee]

I tried it on my break. cleared my sheet 1 and then crashed I did the debug
button and it highlighted this section

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

 

[Joel]

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]

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