Worksheet_Change or Worksheet_Calculate

[reberryjr]

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.

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

 

[reberryjr]

No, it's a true formula =Today(). I want that cell to update every day.

 

[reberryjr]

Ok, I fiddled with the code some more last night and this morning and it "appears" to be working after some testing. I made the following changes:
- Replaced the Case statement with an IF statement
- Changed a formula on the worksheet around some
Here is the code that is working. I'll know if it's working for certain, as I continue to work on the application throughout the day.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.ScreenUpdating = False


Dim ws As Worksheet
Dim LastRow As Long


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
    LastRow = ws.Range("AW" & ws.Rows.Count).End(xlUp).Row
        If ws.Range("AW" & LastRow).Value = "Paid" Or ws.Range("AW" & LastRow).Value = "Late" Then
            ws.Range("A" & LastRow + 1) = "=Today()"
            ws.Range("B" & LastRow + 1) = Now()
            ws.Range("C" & LastRow + 1) = "Update"
            ws.Range("D" & LastRow & ":G" & LastRow).Copy ws.Range("D" & LastRow + 1)
            ws.Range("I" & LastRow & ":N" & LastRow).Copy ws.Range("I" & LastRow + 1)
            ws.Range("P" & LastRow & ":U" & LastRow).Copy ws.Range("P" & LastRow + 1)
            ws.Range("W" & LastRow & ":AB" & LastRow).Copy ws.Range("W" & LastRow + 1)
            ws.Range("AD" & LastRow & ":AI" & LastRow).Copy ws.Range("AD" & LastRow + 1)
            ws.Range("AK" & LastRow & ":AO" & LastRow).Copy ws.Range("AK" & LastRow + 1)
            ws.Range("AP" & LastRow + 1 & ":AT" & LastRow + 1) = 0
            ws.Range("AU" & LastRow & ":AW" & LastRow).Copy ws.Range("AU" & LastRow + 1)
        End If
    End If
Next ws
Application.ScreenUpdating = True


End Sub