Syntax Sucks!

[Bennyton]

Hey all. can anyone tell me whats wrong with this?

Sub StopFind()
'
'
'
Sheets("combine450_60 level").Select
Columns("A:D").Select
Selection.ClearContents
Windows("60 STOP macro.xls").Activate
Range("A:A,G:G").Select
Range("G1").Activate
Selection.Copy
Windows("MATT NOT SAP MACRO1.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("450 STOP macro.XLS").Activate
Range("A:A,G:G").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("MATT NOT SAP MACRO1.xls").Activate
Range("C1").Select
ActiveSheet.Paste
'
'Test to search for stop statuses in "combine450_60 level"
'when it finds a stop status, it compares the serial number next to it, to those in column A in the WIP work sheet
'if it finds a match, it will copy and paste that column into the "stop sap or matt" sheet,
'and delete the row from the WIP sheet
SerialCol = "A"
SerialCol1 = "C"
crit1 = "STOP"

Dim CellR As Range
Dim strName As String
Dim Arr() As String

lastRow1 = Sheets("stop sap or matt").Range("A1").End(xlDown).Row


lastRow2 = Sheets("WIP").Range("A1").End(xlDown).Row

lastRow3 = Sheets("combine450_60 level").Range("A1").End(xlDown).Row
For i = 1 To lastRow3

lastRow4 = Sheets("combine450_60 level").Range("C1").End(xlDown).Row
For h = 1 To lastRow4

If Sheets("combine450_60 level").Cells(i, "B").Value = crit1 Then
For j = 1 To lastRow2
If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(i, SerialCol).Value Then
Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Range(lastRow1 + 1 & ":" & lastRow1 + 1)
Sheets("WIP").Rows(j).EntireRow.Delete

End If

Next j

ElseIf Sheets("combine450_60 level").Cells(h, "D").Value = crit1 Then
For j = 1 To lastRow2
If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(h, SerialCol1).Value Then
Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Rows(lastRow1 + 1 & ":" & lastRow1 + 1)
Sheets("WIP").Rows(j).EntireRow.Delete
End If
Next j
Next h
Next i
End If
End Sub

 

[Bennyton]

Which sheets are in which workbook? You seem to have 3.

the first 3 are in workbook 1, the other two are in separate workbooks, and aren't really causing me any problems

 

[Andrew Poulsom]

So what's the purpose of all the copy/pastes?

 

[Bennyton]

So what's the purpose of all the copy/pastes?

It pastes the data into the workbook... so it can be in one local place, while it scans for matches

 

[Bennyton]

SerialCol = "A"
SerialCol1 = "C"
crit1 = "STOP"

Dim CellR As Range
Dim strName As String
Dim Arr() As String
            
            lastRow1 = Sheets("stop sap or matt").Range("A1").End(xlDown).Row
            
            
            lastRow2 = Sheets("WIP").Range("A1").End(xlDown).Row
            
            lastRow3 = Sheets("combine450_60 level").Range("A1").End(xlDown).Row
            For i = 1 To lastRow3
            

        If Sheets("combine450_60 level").Cells(i, "B").Value = crit1 Then
            For j = 1 To lastRow2
                If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(i, SerialCol).Value Then
                Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Range(lastRow1 + 1 & ":" & lastRow1 + 1)
                Sheets("WIP").Rows(j).EntireRow.Delete

                End If
                  
                Sheets("combine450_60 level").Select
                Columns("A:A").Select
                Range("A16").Activate
1               Selection.Delete Shift:=xlToLeft
                Columns("A:A").Select
                Range("A16").Activate
                Selection.Delete Shift:=xlToLeft
                
                Next j

        If Sheets("combine450_60 level").Cells(i, "B").Value = crit1 Then
            For j = 1 To lastRow2
                If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(i, SerialCol).Value Then
                Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Range(lastRow1 + 1 & ":" & lastRow1 + 1)
                Sheets("WIP").Rows(j).EntireRow.Delete
                End If
                Next j
                
End Sub

should this work better?

 

[Andrew Poulsom]

Lightly tested:

Sub Test()
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Sh3 As Worksheet
    Dim LR As Long
    Dim i As Long
    Dim r As Long
    Set Sh1 = Worksheets("Stop SAP & Matt")
    Set Sh2 = Worksheets("WIP")
    Set Sh3 = Worksheets("Combine450_60 level")
    With Sh3
        LR = .UsedRange.Rows.Count
        For i = 1 To LR
            If .Cells(i, 2).Value = "STOP" Then
                On Error Resume Next
                r = WorksheetFunction.Match(.Cells(i, 1).Value, Sh2.Columns(1), False)
                If Err = 0 Then
                    Sh2.Cells(r, 1).EntireRow.Cut Sh1.Range("A" & Sh1.Rows.Count).End(xlUp).Offset(1)
                Else
                    Err.Clear
                End If
                On Error GoTo 0
            End If
            If .Cells(i, 4).Value = "STOP" Then
                    On Error Resume Next
                    r = WorksheetFunction.Match(.Cells(i, 3).Value, Sh2.Columns(1), False)
                    If Err = 0 Then
                        Sh2.Cells(r, 1).EntireRow.Cut Sh1.Range("A" & Sh1.Rows.Count).End(xlUp).Offset(1)
                    Else
                        Err.Clear
                    End If
                    On Error GoTo 0
            End If
        Next i
    End With
End Sub