Microsoft Excel - copy range to new work book - Asked By John on 08-Nov-12 12:57 PM

HOW TO CHANGE FOR EXCEL 2007. underlined command shows error

Sub Macro1()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+m

    Dim sht As Worksheet, wb As Workbook
    Dim lastrow As Long, counter As Long
    Application.ScreenUpdating = False
    Set wb = Workbooks.Add
    For Each sht In ThisWorkbook.Worksheets
      With sht
        lastrow = .Range("A" & Rows.Count).End(xlsmUp).Row
        .Range("AG1:AG6") = True
        .Range("AG6").Formula = "=I6=X"
        .Range("AG6").AutoFill Destination:=.Range("AG6", .Cells(lastrow, "AG")), Type:=xlFillDefault
        If WorksheetFunction.CountIf(.Range("AG6:AG" & lastrow), True) > 0 Then
          .Columns("AG").AutoFilter Field:=1, Criteria1:="True"
          .Range("A1").CurrentRegion.EntireRow.Copy Destination:=wb.Sheets(1).Range("A" & counter + 1)
          counter = wb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
        End If
      End With
    Next sht
    Application.ScreenUpdating = True
End Sub


Harry Boughen replied to John on 08-Nov-12 05:24 PM
Hello John,

Perhaps you only need xlUp  in the faulty line.