My company uses Excel to track inspections and maintenance of portable fire extinguishers and I'm trying to add some functionality to it but don't have the first clue about how to do it. I'll do my best to explain what I need assistance with:
All three sheets will have the same column headings in row 1. On the first and second sheets in the workbook (titled "Monthly" and "Annual" respectively), I am using conditional formatting to color cells in columns Y and AC based upon there values as determined by a formula (Yellow if the calculation returns a value less than 90, Red if the value returned is 0 or a negative number).
What I would like a macro to do is to copy the entire row from column A through column AD to a third sheet (titled "Maint Due"). It would also be nice if this process were automated so that anytime the values in columns Y and AC changed in the "Monthly" or "Annual" sheets, the information in the "Maint Due" sheet was automatically updated (but if I have to rerun the macro manually for that to happen it's not a big deal).
Hopefully I've explained what I'm trying to do in a way that it makes sense to someone and they can offer help. I have very little coding experience and it's been 15 years since I've really done any coding so I'm very rusty. I appreciate any advice or assistance folks may be able to give.!
EDIT:
I've never used the Macro recorder and I can only figure out how to create a macro to copy and paste, so I had no luck there. After doing some more searching and watching some videos I cobbled this together:
Sub Show_on_Maint()
x = 2
'Sets the starting row
Do While Cells(x, 2) <> ""
'Continue to evaluate until a blank cell is reached
If Cells(x, 25) <= 90 Then
'Evaluates the cell in column Y to determine if the value is less than or equal to 90
Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")
'Copies the row to the Maint Due sheet
Else
If Cells(x, 29) <= 90 Then
'Evaluates the cell in column AC to determine if the value is less than or equal to 90
Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")
'Copies the row to the Maint Due sheet
End If
End If
x = x + 1
Loop
End Sub
When I run/debug it I get a Loop without Do error. I think my logic is sound but I don't have enough experience to figure out why I'm getting that error.
EDIT: Missing End If before x = x + 1
Now I receive a runtime error 9 "Subscript out of range" at:
Sheets("Sheet1").Rows(x).Copy Sheets("Sheet6").Range("A2")