Sub MoveRowsToNewSheet()
'========================================================================
' COPIES ALL ROWS WHERE COLUMN WITH "Total" IN ROW 9 HAS VALUE NOT EQUAL TO 0
' FROM SHEET1 to A12 AND BELOW ON SHEET2
'========================================================================
Dim LRMain As Long, LRNew As Long, i As Long, TotalCol As Long
Worksheets("Sheet1").Select
LRMain = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
TotalCol = Worksheets("Sheet1").Range("A9:AA9").Find("Total", Range("a9"), xlValues, xlWhole, xlByColumns, xlNext).Column
With Worksheets("Sheet1")
For i = 10 To LRMain Step 1
If Cells(i, TotalCol).Value <> 0 Then
LRNew = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
If LRNew < 12 Then LRNew = 12
'Cells(i, 1).Copy Worksheets("Sheet2").Range("A" & LRNew + 1)
Cells(i, 1).Copy
Sheets("Sheet2").Range("A" & LRNew + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Cells(i, TotalCol).Copy Worksheets("Sheet2").Range("b" & LRNew + 1)
Cells(i, TotalCol).Copy
Sheets("Sheet2").Range("b" & LRNew + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i
End With
Worksheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Select
End Sub