Rows not deleting after copy (Excel Macro)

Question

I am a novice with excel macro's and VBA and I am trying to automate the manipulation of daily work sheets. the amount of data copied to sheet 2 can vary daily as can the number of items that need to be stripped from sheet 1.

I have came up with the following macro which IS working as I would like in terms of the selection and moving the information to sheet 2, however the information is then not being deleted from sheet 1. Can someone identify why this is happening please?

ActiveSheet.Range("$A$1:$AD$5101").AutoFilter Field:=12, Criteria1:=Array( _
        "11111", "11112", "11113", "11114", "11115", "11116", "11117", "11118", "11119", "11120" _
        , "11121", "11122", "11123", "11124", "11125", "11126", "11127", "11128", "11129", _
        "11130", "11131", "11132", "11133", "11134", "11135", "11136", "11137", "11138", "11139" _
        , "11140", "11141", "11142", "11143", "11144", "11145", "11146", "11147", "11148", _
        "11149", "11150"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$AD$4184").AutoFilter Field:=13, Criteria1:= _
        "<>*Local (71)*", Operator:=xlAnd
        Dim r1ng As Range
    Set r1ng = Sheets("Sheet 1").UsedRange
    With ActiveSheet.AutoFilter.Range
    .Offset(1, 0).Resize(.Rows.Count - 1).Copy
End With
    Sheets("Sheet 2").Select
    On Error Resume Next
    Dim xCell As Range
    For Each xCell In ActiveSheet.Columns(1).Cells
        If Len(xCell) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
    ActiveSheet.Paste
    Sheets("Sheet 1").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    ActiveSheet.ShowAllData

I suggest reading this for starters https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba And specify the sheet rather than using `ActiveSheet`. — SJR, Jul 17 '23 at 11:17
And note that selecting a sheet does not select any cells (other than A1 by default). — SJR, Jul 17 '23 at 11:24

score 0 · Answer 1 · answered Jul 17 '23 at 15:53

There are a lot of opinions about Select, Activesheet, Activate. Worth investigating them and shape your own. To mention Select is the same when you click the Sheet-tab at the bottom of the page. To your code try this. Replace this line

    .Offset(1, 0).Resize(.Rows.Count - 1).Copy

with this

    .Offset(1, 0).Resize(.Rows.Count - 1).Select
    Selection.Copy

Rows not deleting after copy (Excel Macro)

1 Answers1