VBA Code Streamline

[Benzoli7]

Hello,
Can anyone help me streamline this code? I want to avoid "Selecting" and "Activating" as much as possible because this block of code will be repeated several times with different columns. Thanks so much.

Sheets("Rate Comparison").Activate
    Range("A6").AutoFilter
    Range("A6").AutoFilter Field:=26, _
        Criteria1:="=New"
    Range("A7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Windows("Rate Comparison Macro1").Activate
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[JackDanIce]

Try:

With Sheets("Rate Comparision")
    .Range("A6").AutoFilter
    .Range("A6").AutoFilter Field:=26, Criteria1:="=New"
    FinalRow = .Range("A7").End(xlDown).Row
    .Range("A7:A" & FinalRow).SpecialCells(xlCellTypeVisible).Copy
End With
Windows("Rate Comparison Macro1").Activate
With Sheets("Proposed Ranking")
    FinalRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    .Range("C" & FinalRow).PasteSpecial Paste:=xlPasteValues
End With

 

[Benzoli7]

Thanks so much. That looks much cleaner.

Any advice on speeding up this next bit of code? It is applied to anywhere between 50,000 and 60,000 rows. Screen updating is off and calculation is manual but, it still takes several minutes to process.

Sheets("Proposed Ranking").Select
    Range("T1").Value = "COUNTIF"
    Range("T2").Formula = _
            "=COUNTIF('Rate Comparison'!$K$7:K15,B2)"
    LR = Range("A" & Rows.Count).End(xlUp).Row
     Range("T2").AutoFill Destination:=Range("T2:T" & LR), Type:=xlFillDefault
    Calculate

Also, is there any way to make this line look at all rows in column K?

"=COUNTIF('Rate Comparison'!$K$7:K15,B2)"

Thanks again.

 

[JackDanIce]

Try:

With Sheets("Proposed Ranking")
    .Range("T1") = "COUNTIF"
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("T2").Formula = "=COUNTIF('Rate Comparison'!$K$7:$K$" & LR & ",B2)"
    .Range("T2:T" & LR).FillDown
End With