I'm trying to scan through the worksheet and if the worksheet "Testing1" the "status" column showed "Closed" then copy the entire row to worksheet "Sheet2" and paste it there with similar header. i keep getting subscript over range message.
Sub MainMacro()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Testing1")
Set ws2 = Sheets("Sheet2")
Dim lastrow As Long, lastcol As Long, icol As Long
Dim icell As Range
lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
lastcol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
For icol = 1 To lastcol
For Each icell In ws1.Range(ws1.Cells(2, icol), ws1.Cells(lastrow, icol))
If icell.Value = "Closed" Then
icell.EntireRow.Copy _
Destination:=Sheets(ws2.Cells(2, icol).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next icell
Next icol
End Sub
Bookmarks