I have a spreadsheet in which the data changes daily (As far as number of
rows in each section)

Basically the data I want to sort is in columns A through D

I need a Macro to sort based on Column C from Largest Number to Smallest
Number, and delete any rows in which the number in Column C is less than but
not equal to 5.

Then copy what is in Column B for each row to another spreadsheet and
transpose from the column form to a single row.

The catch is the information that needs to be sorted is in the middle of a
bunch of other data, so, I would guess I could use some sort of code to match
the number "0" in column D for each row to sort and copy...

Hope this makes sense, thank you for your help!

I used autofilter on columns C and D

Sub Macro1()
'
Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

With SourceSht
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlNo, _
key1:=.Range("D1"), _
order1:=xlAscending, _
key2:=.Range("C1"), _
order2:=xlAscending



.Range("C1:D" & LastRow).AutoFilter
.Range("C1:D" & LastRow).AutoFilter _
Field:=1, _
Criteria1:="=5"
.Range("C1:D" & LastRow).AutoFilter _
Field:=2, _
Criteria1:="=0"

Set CopyRange = .Range("B1:B" & LastRow) _
.SpecialCells(Type:=xlCellTypeVisible)
CopyRange.Copy
DestSht.Range("A1").PasteSpecial , _
Paste:=xlPasteValues, _
Transpose:=True
End With
End Sub


"GoodTrouble" wrote:

I have a spreadsheet in which the data changes daily (As far as number of
rows in each section)

Basically the data I want to sort is in columns A through D

I need a Macro to sort based on Column C from Largest Number to Smallest
Number, and delete any rows in which the number in Column C is less than but
not equal to 5.

Then copy what is in Column B for each row to another spreadsheet and
transpose from the column form to a single row.

The catch is the information that needs to be sorted is in the middle of a
bunch of other data, so, I would guess I could use some sort of code to match
the number "0" in column D for each row to sort and copy...

Hope this makes sense, thank you for your help!

Thanks Joel, with a few modifications it works perfect! You saved me a lot of
hassle and time!

"Joel" wrote:

I used autofilter on columns C and D

Sub Macro1()
'
Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

With SourceSht
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlNo, _
key1:=.Range("D1"), _
order1:=xlAscending, _
key2:=.Range("C1"), _
order2:=xlAscending



.Range("C1:D" & LastRow).AutoFilter
.Range("C1:D" & LastRow).AutoFilter _
Field:=1, _
Criteria1:="=5"
.Range("C1:D" & LastRow).AutoFilter _
Field:=2, _
Criteria1:="=0"

Set CopyRange = .Range("B1:B" & LastRow) _
.SpecialCells(Type:=xlCellTypeVisible)
CopyRange.Copy
DestSht.Range("A1").PasteSpecial , _
Paste:=xlPasteValues, _
Transpose:=True
End With
End Sub


"GoodTrouble" wrote:

I have a spreadsheet in which the data changes daily (As far as number of
rows in each section)

Basically the data I want to sort is in columns A through D

I need a Macro to sort based on Column C from Largest Number to Smallest
Number, and delete any rows in which the number in Column C is less than but
not equal to 5.

Then copy what is in Column B for each row to another spreadsheet and
transpose from the column form to a single row.

The catch is the information that needs to be sorted is in the middle of a
bunch of other data, so, I would guess I could use some sort of code to match
the number "0" in column D for each row to sort and copy...

Hope this makes sense, thank you for your help!