Data sorting to a new sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Novice Lee - 25 Jul 2008 00:11 GMT

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

Reply to this Message

Joel - 25 Jul 2008 02:36 GMT

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?

> 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.
[quoted text clipped - 28 lines]
>
> Thanks for your input

Reply to this Message

Novice Lee - 25 Jul 2008 15:13 GMT

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

> This doesn't sound too difficult. I just confused by the sheet numbers in
> your description
[quoted text clipped - 37 lines]
> >
> > Thanks for your input

Reply to this Message

Joel - 25 Jul 2008 16:36 GMT

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

> Morning
>
[quoted text clipped - 64 lines]
> > >
> > > Thanks for your input

Reply to this Message

Novice Lee - 25 Jul 2008 18:13 GMT

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

> Sub MoveData()
>
[quoted text clipped - 43 lines]
> End With
> End Sub

Reply to this Message

Joel - 25 Jul 2008 19:49 GMT

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.

> If I change the Sheet name (sheet 2) to ALD Would I change Sheets(2) to ALD?
>
[quoted text clipped - 45 lines]
> > End With
> > End Sub

Reply to this Message

Novice Lee - 25 Jul 2008 20:26 GMT

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

> the sheet names can be iether of the following
>
[quoted text clipped - 59 lines]
> > > End With
> > > End Sub

Reply to this Message

Novice Lee - 25 Jul 2008 19:10 GMT

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

> Sub MoveData()
>
[quoted text clipped - 112 lines]
> > > >
> > > > Thanks for your input

Reply to this Message