Looking for some guidance here. I have a workbook that has a dynamic amount of sheets. As of now, I have 4 static sheets and 2 test sheets that are part of the dynamic volume. Each of the test sheets (I'll refer to them as Client sheets going forward), has 44 columns. I'd like to implement some code that runs across all of the dynamic sheets, and when the value of the last row in column AR changes to either "Paid" or "Late" (via a formula), the current row is copied and pasted to the row below it.
I'm relatively new to VBA and have pieced this code together from things I've seen in this, and other forums. I realize that the below code is only changing the value of one cell, but I figured I would start out small and if the code worked, I would expand on it. One thing that seems particularly odd is, when I hit F5 to run the code, I keep getting prompted to save the macro, which hasn't ever happened with any of the other code that I've written.
I'm relatively new to VBA and have pieced this code together from things I've seen in this, and other forums. I realize that the below code is only changing the value of one cell, but I figured I would start out small and if the code worked, I would expand on it. One thing that seems particularly odd is, when I hit F5 to run the code, I keep getting prompted to save the macro, which hasn't ever happened with any of the other code that I've written.
Code:
Private Sub WorkSheetCalculate()
Dim ws As Worksheet
Dim LastRow As Long
Dim NextRow As Long
LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
NextRow = ws.Range("D" & Rows.Count).End(xlUp).Row + 1
For Each ws In Worksheets
If Not ws.Name = "Bios" And Not ws.Name = "Stats" And Not ws.Name = "Financials" And Not ws.Name = "Variables" Then
If Target = ws.Range("AR" & LastRow) Then
If InStr(1, Range("AR" & LastRow), "Paid") Then
Range("A" & NextRow) = "=Today()"
ElseIf InStr(1, Range("AR" & LastRow), "Late") Then
Range("A" & NextRow) = "=Today()"
Else
Range("A" & NextRow) = ""
End If
End If
End If
Next ws
End Sub