in need of a macro for a complex task

[Tonyk1051]

Delete row 1 in sheet 1
Highlight column H and insert 2 new columns (do the same for sheet 2)
Highlight column X and insert 1 new column (do the same for sheet 2)

Highlight all of sheet 2 apply data filter

On column G
click select all, check boxes that have Manufacture, Working but missing parts,
tested confirmed defective, tested confirmed damage, picture of defect done

all the data that is found, cut and move to sheet 1

Now on sheet 1

highlight all of it and apply data filter
go to coulmn L and search for TT
all the data that it pulls delete entire row
now search for tv
all the data that it pulls delete entire row
unfilter sheet 1


instructions are always the same but data amount is always
different sometime less some times more, is this something a Macro can do?

 

[Tonyk1051]

I can get the macro code to do the steps its just im worried it ll mess up since the data amount is never the same.

 

[mumps]

Try:

Sub tonyk()
    Application.ScreenUpdating = False
    Dim LastRow As Long, WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = Sheets("Not on a Category")
    Set WS2 = Sheets("On a Category")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With WS1
        .Rows(1).Delete
        .Columns("H:I").Insert Shift:=xlToRight
        .Columns("X:X").Insert Shift:=xlToRight
    End With
    With WS2
        .Columns("H:I").Insert Shift:=xlToRight
        .Columns("X:X").Insert Shift:=xlToRight
    End With
    With WS2
        .Range("A1", .Range("AA" & .Rows.Count).End(xlUp)).AutoFilter Field:=7, Criteria1:=Array( _
        "MANUFACTURE", "PICTURE OF DEFECT DONE", "TESTED CONFIRMED DAMAGED", _
        "TESTED CONFIRMED DEFECTIVE", "WORKING BUT MISSING PARTS"), Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).Copy WS1.Cells(WS1.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    With WS1
        .Range("A1", .Range("AB" & .Rows.Count).End(xlUp)).AutoFilter Field:=12, Criteria1:="=*TT*", Operator:=xlAnd, Criteria1:="=*tv*"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Tonyk1051]

Try:

Sub tonyk()
    Application.ScreenUpdating = False
    Dim LastRow As Long, WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = Sheets("Not on a Category")
    Set WS2 = Sheets("On a Category")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With WS1
        .Rows(1).Delete
        .Columns("H:I").Insert Shift:=xlToRight
        .Columns("X:X").Insert Shift:=xlToRight
    End With
    With WS2
        .Columns("H:I").Insert Shift:=xlToRight
        .Columns("X:X").Insert Shift:=xlToRight
    End With
    With WS2
        .Range("A1", .Range("AA" & .Rows.Count).End(xlUp)).AutoFilter Field:=7, Criteria1:=Array( _
        "MANUFACTURE", "PICTURE OF DEFECT DONE", "TESTED CONFIRMED DAMAGED", _
        "TESTED CONFIRMED DEFECTIVE", "WORKING BUT MISSING PARTS"), Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).Copy WS1.Cells(WS1.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    With WS1
        .Range("A1", .Range("AB" & .Rows.Count).End(xlUp)).AutoFilter Field:=12, Criteria1:="=*TT*", Operator:=xlAnd, Criteria1:="=*tv*"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Hi Mumps,

It defintely works just that its missing a few things before i can check the soultion found, i had to highlight column w and insert a new column
and the auto filter did take out rows that had shelves TT but shelves with TV was still there....once i upload the new code you post, it'll work no matter how much data there is right? thanks in advance

 

[MARK858]

with TV was still there

That is because in your question you have asked for tv not TV.

 

[Tonyk1051]

That is because in your question you have asked for tv not TV.

Yes, thats my fault, apologies

 

[MARK858]

No need to apologise. Is it always TT and TV or are they ever lowercase?

 

[MARK858]

I think that you also need to change xlAnd to xlOr

 

[Tonyk1051]

No need to apologise. Is it always TT and TV or are they ever lowercase?

no its never lower always upper case

 

[MARK858]

Don't miss my last post as we posted at roughly the same time