Selected Answer
Tubrak
I see two problems with your code- you are copying a discontinuous range but you are trying to paste it into row lr of SH1 (and remember that is something like 740 from SH1!).
In the modified code below, I've set a new variable Lrw for the last row in SH2 and used the Union method to select the cells. On Sh2 I've added headers in row 1 (and code to clear the results before the next run). Main changes/ comments are in bold:
Sub MM1()
Dim r As Long, lr As Long, Lrw As Long
Dim WS As Worksheet, SH As Worksheet
Set WS = Sheets("SH1"): Set SH = Sheets("SH2")
'Get last rows per sheet
lr = WS.Cells(Rows.Count, "A").End(xlUp).Row
Lrw = SH.Cells(Rows.Count, "A").End(xlUp).Row
' Stop refresh (for speed)
Application.ScreenUpdating = False
' Clear last results
SH.Range("A2:E" & Lrw).ClearContents
For r = 2 To lr
If WS.Range("g" & r).Value = SH.Range("g1").Value And WS.Range("h" & r).Value = SH.Range("h1").Value Then
With WS
Union(.Range("A" & r & ":B" & r), .Range("E" & r & ":F" & r), .Range("S" & r)).Copy '
End With
With Sheets("SH2")
'Get new last row for this sheet
Lrw = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A" & Lrw + 1).PasteSpecial xlPasteValues
End With
End If
Next r
' Remove copy dotted line and refresh screen
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Brought " & Lrw & " results to sheet SH2"
End Sub
I also put this code behind your "BRING" button on SH2:
Private Sub CommandButton1_Click()
Call MM1
End Sub
Hope this makes sense and fixes your problem.