Copy / Display row from another sheet
I have a workbook with 10 sheets. 4 Columns in each sheet. "A" is the "QTY" column, which is "0" by default.
When I change the QTY from 0 to 1, the macro copies that row to my "Project" destination sheet.
However, I'm having trouble getting a few features to work.
First, How can I get the Macro to run all the time? or at certain time intervals?
Second, Is there a way to have the macro remove the row from the "project" sheet if the QTY has been changed back to 0? If this is not possible or a really long code; is there a way to have all the rows with >0 in the "QTY" to be simply displayed on my "Project" worksheet and not fully copied? (Probably not).
The reason being, when I run the Macro a second time, It re-copies the rows it already copied (since it's just adding them to the "Project" sheet at the last row).
Here is the code I'm working with, but I had some help with it so I've been having trouble figuring this all out. I'm new to VBA.
Code:
Option Explicit
Sub AddRowContinueOn()
Dim ws As Worksheet, cws As Worksheet
Dim cLRow As Integer, sLRow As Integer
Set cws = Worksheets("Complete")
cLRow = cws.Cells(cws.Rows.Count, "A").End(xlUp).row + 1
Dim i As Integer, val As Integer
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Complete" Then
sLRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
For i = 2 To sLRow
val = CInt(ws.Cells(i, "A").Value)
If val <> 0 Then
cws.Range("A" & cLRow & ":D" & cLRow).Value = ws.Range("A" & i & ":D" & i).Value
cLRow = cLRow + 1
End If
Next
End If
Next
End Sub