Creating a New Sheet with all relevant data from a different table.

[steveh8204]

Hi,

is it possible to create code to go through a table and if a value is met transfer the whole contents of the row into a new sheet totalling all relevant values.

For example creating a sheet with all the row information for every row that has the value in Column F higher than 0?

Thanks in advance.

 

[steveh8204]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet
    Set srcWS = Sheets("SAP")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("F1:F" & LastRow).AutoFilter Field:=1, Criteria1:="<>0", Criteria2:="<>"
        .Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Issues").Cells(Sheets("Issues").Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("F1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Works like a dream, thanks very much!

 

[mumps]

You are very welcome.