Hi
I have the below code which is extracting data from one sheet onto another, and then in the next column producing a list of the unique values.
However, it appears whatever is in the first cell on the dta sheet (ie F12) is always being duplicated. I need to ignore this. I cannot simply change to F11 (as has column heading I do not want) - any tips please?
Sub Macro1()
'
' Macro1 Macro
'
Sheets("Sheet4").Select
Columns("A:B").Select
Selection.ClearContents
Sheets("MCC").Select
Range("F11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet4").Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Range("A2:A125").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"B2"), Unique:=True
Range("B2:bc16").Select
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("bc1:bc16") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet4").Sort
.SetRange Range("B2:bc16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sheet1").Select
'
End Sub
I have the below code which is extracting data from one sheet onto another, and then in the next column producing a list of the unique values.
However, it appears whatever is in the first cell on the dta sheet (ie F12) is always being duplicated. I need to ignore this. I cannot simply change to F11 (as has column heading I do not want) - any tips please?
Sub Macro1()
'
' Macro1 Macro
'
Sheets("Sheet4").Select
Columns("A:B").Select
Selection.ClearContents
Sheets("MCC").Select
Range("F11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet4").Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Range("A2:A125").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"B2"), Unique:=True
Range("B2:bc16").Select
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("bc1:bc16") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet4").Sort
.SetRange Range("B2:bc16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sheet1").Select
'
End Sub