0

I am trying to write a VBA code that will filter a report in sheet 'New Business Data' based on Column A. Then, it should paste the results from Column B to column K in Sheet 'New Sales' However, only part of the data is coming through because there may be some empty cells in columns C-J. I know the issue is with the Range(Selection...) but not sure how to correct

'Stage 6
Sheets("New Sales").Select
Range("B1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "Accounts Live - Stage 6"

Sheets("New Business Data").Select
ActiveSheet.Range("$A$1:$K$122").AutoFilter Field:=1, Criteria1:= _
    "Stage 6 - Live"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("New Sales").Select
Range("B1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Range("B1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "Total"
John Angerami
  • 1
  • You dont need to keep selecting everything, you can use UsedRange.SpecialCells(xlCellTypeVisible).copy to copy the range – Nathan_Sav Jan 12 '16 at 16:32

1 Answers1

1

This should help to clean up your code a bit. I've opted to only paste the filtered values from the New Business Data worksheet's A:K once.

Sub stage_6()

    'Stage 6
    With Worksheets("New Sales")
        .Range("B1").End(xlDown).Offset(1, 0) = "Accounts Live - Stage 6"
    End With

    With Worksheets("New Business Data")
        With .Range("$A$1").CurrentRegion
            With .Resize(.Rows.Count, 11)
                .AutoFilter Field:=1, Criteria1:="Stage 6 - Live"
                .Copy Destination:=Worksheets("New Sales").Range("B1").End(xlDown).Offset(1, 0)
            End With
        End With
        .Range("B1").End(xlDown).Offset(1, 0) = "Total"
    End With

End Sub

This should get you started with writing code that avoids the use of .Select and `.Activate.

¹ See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1