Need Help with MACRO to do CRAZY thing,
 

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

Need Help with MACRO to do CRAZY thing,
 

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

Need Help with MACRO to do CRAZY thing,
 

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

Need Help with MACRO to do CRAZY thing,
 

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

Need Help with MACRO to do CRAZY thing,
 

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

Need Help with MACRO to do CRAZY thing,
 

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