Hello All,

First you guys are great with fast reponse! This time, I'd like you to help
me with this crazy sorting, copy and paste.
Example:

ColA ColB ColC .......
ColJ
Row 5 FileName SampleType SampleName Calculated Conc
Row 6 080808-1 Blank Sample Test1
10
Row 7 080808-2 Standard Test2
15
Row 8 080808-3 Uknown Sample Test3 15

Here are what I need your help,

1/Row 5 is the headers as you can see
2/Sort (sample type)
3/Freezepanes at range("D6")
4/If col B contain the word (Unknown Sample), copy value in column J
(Caculated conc) and put the sheet called RAWDATA.
5/Repeat other sheets (other sheets also have same exatly format).

Thanks

Sub sortdata()

RawDataRow = 1
For Each sht In Sheets
If UCase(sht.Name) < "RAWDATA" Then
With sht
'Get Last row fo data
LastRow = .Range("A" & Rows.Count).End(xlUp)
.Rows("6:" & LastRow).Sort _
Key1:=.Range("C6"), _
Order1:=xlAscending, _
Header:=xlNo
Set SearchRange = .Range("B6:B" & LastRow)
For Each cell In SearchRange
If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then
Sheets("RAWDATA").Range("A" & RawDataRow) = _
.Range("J" & cell.Row)
RawDataRow = RawDataRow + 1
End If
Next cell
End With
End If
Next sht

End Sub

"bioyyy" wrote:

Hello All,

First you guys are great with fast reponse! This time, I'd like you to help
me with this crazy sorting, copy and paste.
Example:

ColA ColB ColC .......
ColJ
Row 5 FileName SampleType SampleName Calculated Conc
Row 6 080808-1 Blank Sample Test1
10
Row 7 080808-2 Standard Test2
15
Row 8 080808-3 Uknown Sample Test3 15

Here are what I need your help,

1/Row 5 is the headers as you can see
2/Sort (sample type)
3/Freezepanes at range("D6")
4/If col B contain the word (Unknown Sample), copy value in column J
(Caculated conc) and put the sheet called RAWDATA.
5/Repeat other sheets (other sheets also have same exatly format).

Thanks

Joel:

Thanks for code. However, I got an error (Run-time error '13': Type
mismatch). Thanks,



"Joel" wrote:

Sub sortdata()

RawDataRow = 1
For Each sht In Sheets
If UCase(sht.Name) < "RAWDATA" Then
With sht
'Get Last row fo data
LastRow = .Range("A" & Rows.Count).End(xlUp)
.Rows("6:" & LastRow).Sort _
Key1:=.Range("C6"), _
Order1:=xlAscending, _
Header:=xlNo
Set SearchRange = .Range("B6:B" & LastRow)
For Each cell In SearchRange
If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then
Sheets("RAWDATA").Range("A" & RawDataRow) = _
.Range("J" & cell.Row)
RawDataRow = RawDataRow + 1
End If
Next cell
End With
End If
Next sht

End Sub

"bioyyy" wrote:

Hello All,

First you guys are great with fast reponse! This time, I'd like you to help
me with this crazy sorting, copy and paste.
Example:

ColA ColB ColC .......
ColJ
Row 5 FileName SampleType SampleName Calculated Conc
Row 6 080808-1 Blank Sample Test1
10
Row 7 080808-2 Standard Test2
15
Row 8 080808-3 Uknown Sample Test3 15

Here are what I need your help,

1/Row 5 is the headers as you can see
2/Sort (sample type)
3/Freezepanes at range("D6")
4/If col B contain the word (Unknown Sample), copy value in column J
(Caculated conc) and put the sheet called RAWDATA.
5/Repeat other sheets (other sheets also have same exatly format).

Thanks

Try changing:

LastRow = .Range("A" & Rows.Count).End(xlUp)
to
LastRow = .Range("A" & Rows.Count).End(xlUp).Row



bioyyy wrote:

Joel:

Thanks for code. However, I got an error (Run-time error '13': Type
mismatch). Thanks,

"Joel" wrote:

Sub sortdata()

RawDataRow = 1
For Each sht In Sheets
If UCase(sht.Name) < "RAWDATA" Then
With sht
'Get Last row fo data
LastRow = .Range("A" & Rows.Count).End(xlUp)
.Rows("6:" & LastRow).Sort _
Key1:=.Range("C6"), _
Order1:=xlAscending, _
Header:=xlNo
Set SearchRange = .Range("B6:B" & LastRow)
For Each cell In SearchRange
If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then
Sheets("RAWDATA").Range("A" & RawDataRow) = _
.Range("J" & cell.Row)
RawDataRow = RawDataRow + 1
End If
Next cell
End With
End If
Next sht

End Sub

"bioyyy" wrote:

Hello All,

First you guys are great with fast reponse! This time, I'd like you to help
me with this crazy sorting, copy and paste.
Example:

ColA ColB ColC .......
ColJ
Row 5 FileName SampleType SampleName Calculated Conc
Row 6 080808-1 Blank Sample Test1
10
Row 7 080808-2 Standard Test2
15
Row 8 080808-3 Uknown Sample Test3 15

Here are what I need your help,

1/Row 5 is the headers as you can see
2/Sort (sample type)
3/Freezepanes at range("D6")
4/If col B contain the word (Unknown Sample), copy value in column J
(Caculated conc) and put the sheet called RAWDATA.
5/Repeat other sheets (other sheets also have same exatly format).

Thanks




--

Dave Peterson

Dave,

Thanks. That was fixed. However there is an error for

If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then

(Run-time error '13': Type mismatch)

Thanks,


"Dave Peterson" wrote:

Try changing:

LastRow = .Range("A" & Rows.Count).End(xlUp)
to
LastRow = .Range("A" & Rows.Count).End(xlUp).Row



bioyyy wrote:

Joel:

Thanks for code. However, I got an error (Run-time error '13': Type
mismatch). Thanks,

"Joel" wrote:

Sub sortdata()

RawDataRow = 1
For Each sht In Sheets
If UCase(sht.Name) < "RAWDATA" Then
With sht
'Get Last row fo data
LastRow = .Range("A" & Rows.Count).End(xlUp)
.Rows("6:" & LastRow).Sort _
Key1:=.Range("C6"), _
Order1:=xlAscending, _
Header:=xlNo
Set SearchRange = .Range("B6:B" & LastRow)
For Each cell In SearchRange
If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then
Sheets("RAWDATA").Range("A" & RawDataRow) = _
.Range("J" & cell.Row)
RawDataRow = RawDataRow + 1
End If
Next cell
End With
End If
Next sht

End Sub

"bioyyy" wrote:

Hello All,

First you guys are great with fast reponse! This time, I'd like you to help
me with this crazy sorting, copy and paste.
Example:

ColA ColB ColC .......
ColJ
Row 5 FileName SampleType SampleName Calculated Conc
Row 6 080808-1 Blank Sample Test1
10
Row 7 080808-2 Standard Test2
15
Row 8 080808-3 Uknown Sample Test3 15

Here are what I need your help,

1/Row 5 is the headers as you can see
2/Sort (sample type)
3/Freezepanes at range("D6")
4/If col B contain the word (Unknown Sample), copy value in column J
(Caculated conc) and put the sheet called RAWDATA.
5/Repeat other sheets (other sheets also have same exatly format).

Thanks




--

Dave Peterson

There was a typo.

I'd use this (untested, but compiled):

Option Explicit
Sub sortdata()

Dim RawDataRow As Long
Dim LastRow As Long
Dim Sht As Worksheet
Dim SearchRange As Range
Dim Cell As Range

RawDataRow = 1
For Each Sht In Worksheets
If UCase(Sht.Name) < "RAWDATA" Then
With Sht
'Get Last row fo data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("6:" & LastRow).Sort _
Key1:=.Range("C6"), _
Order1:=xlAscending, _
Header:=xlNo
Set SearchRange = .Range("B6:B" & LastRow)
For Each Cell In SearchRange
If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then
Sheets("RAWDATA").Range("A" & RawDataRow) = _
.Range("J" & Cell.Row)
RawDataRow = RawDataRow + 1
End If
Next Cell
End With
End If
Next Sht

End Sub



bioyyy wrote:

Dave,

Thanks. That was fixed. However there is an error for

If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then

(Run-time error '13': Type mismatch)

Thanks,

"Dave Peterson" wrote:

Try changing:

LastRow = .Range("A" & Rows.Count).End(xlUp)
to
LastRow = .Range("A" & Rows.Count).End(xlUp).Row



bioyyy wrote:

Joel:

Thanks for code. However, I got an error (Run-time error '13': Type
mismatch). Thanks,

"Joel" wrote:

Sub sortdata()

RawDataRow = 1
For Each sht In Sheets
If UCase(sht.Name) < "RAWDATA" Then
With sht
'Get Last row fo data
LastRow = .Range("A" & Rows.Count).End(xlUp)
.Rows("6:" & LastRow).Sort _
Key1:=.Range("C6"), _
Order1:=xlAscending, _
Header:=xlNo
Set SearchRange = .Range("B6:B" & LastRow)
For Each cell In SearchRange
If UCase(Cells.Value) = "UNKNOWN SAMPLE" Then
Sheets("RAWDATA").Range("A" & RawDataRow) = _
.Range("J" & cell.Row)
RawDataRow = RawDataRow + 1
End If
Next cell
End With
End If
Next sht

End Sub

"bioyyy" wrote:

Hello All,

First you guys are great with fast reponse! This time, I'd like you to help
me with this crazy sorting, copy and paste.
Example:

ColA ColB ColC .......
ColJ
Row 5 FileName SampleType SampleName Calculated Conc
Row 6 080808-1 Blank Sample Test1
10
Row 7 080808-2 Standard Test2
15
Row 8 080808-3 Uknown Sample Test3 15

Here are what I need your help,

1/Row 5 is the headers as you can see
2/Sort (sample type)
3/Freezepanes at range("D6")
4/If col B contain the word (Unknown Sample), copy value in column J
(Caculated conc) and put the sheet called RAWDATA.
5/Repeat other sheets (other sheets also have same exatly format).

Thanks




--

Dave Peterson


--

Dave Peterson