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" 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
>
>
> "Novice Lee" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
|
Similar Threads
