Sort / Copy Macro
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!
|