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]

I tried to edit the post before anyone responded, but my 10 minute delay hadn't expired. Yes, AW is now the last column (formerly "AR"). When I step through the code, it works fine. However, when I update the cells that AW feeds off of (I tried manual updates and updates via form), to determine "Late" or "Paid", the code doesn't appear to run.

 

[mumps]

Try placing the code in a Worksheet_Activate event. The macro would be triggered when you activate the sheet.

 

[reberryjr]

Made the change and no difference.

 

[reberryjr]

I even tried it this way as well. I put this code in the "ThisWorkbook" section.

Private Sub WorkSheet_Calculate()

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" Then
            Call AddNextFinancialRow
        ElseIf ws.Range("AW" & LastRow).Value = "Late" Then
            Call AddNextFinancialRow
        End If
    End If
Next ws
Application.ScreenUpdating = True
End Sub

This is the macro being called:

Sub AddNextFinancialRow()' AddNextFinancialRow Macro
    Range("A2:AW2").Select
    Selection.Copy
    Range("A3").Select
    ActiveSheet.Paste
    Range("C3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Update"
    Range("V3").Select
    Selection.ClearContents
    Range("V3").Select
    Selection.ClearContents
    Range("O3").Select
    Selection.ClearContents
    Range("AC3").Select
    Selection.ClearContents
    Range("AJ3").Select
    Selection.ClearContents
End Sub

The macro wouldn't work long term as it's structured, but it was a shot in the dark.

 

[MARK858]

Why would you put worksheet event code i.e. WorkSheet_Calculate in ThisWorkbook?
Worksheet event code goes in a sheet module or maybe you meant Workbook_SheetCalculate(ByVal Sh As Object), although would you really want it triggered every time the workbook calculates?.

The other sub goes in a regular module.

2nd sub tidied a bit..

Sub AddNextFinancialRow()    ' AddNextFinancialRow Macro
    Range("A2:AW2").Copy Range("A3")
    Range("C3,V3,O3,AC3,AJ3").ClearContents
End Sub

 

[reberryjr]

Well, I got the original code from different forums and sort of pieced it together. I'm still new to VBA. The reason the code isn't tied to a specific sheet is, the sheets are very dynamic. Every time a Client is added, a new sheet is created, that would need the code run against it.

 

[MARK858]

Then at worst you would need

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

or

Private Sub Workbook_NewSheet(ByVal Sh As Object)

which do go in ThisWorkbook not

WorkSheet_Calculate

 

[reberryjr]

Perhaps I'm not grasping the recommendation. The desire is that as the formula in "AW" updates the cell to either "Paid" or "Late", code is run that copies the active row (absent a few columns) and pastes the contents in the next row. The formula in "AW" can be influenced by 1 of 2 events. Event 1 - The value in "A2" (which is the Today() formula) becomes greater than one of the 5 Due Dates AND the Total Paid is < Amount Due, OR Event 2 - Total Paid = Amount Due. Event 2 would occur when the User enters data onto the respective sheet, via a UserForm.

 

[reberryjr]

I did switch the code to Workbook_SheetActivate. The issue is that now, when I create a new Client, I'm getting 3 extra rows of data, instead of the expected 1 row.

 

[MARK858]

Posted before seeing OP's last post.

which is the Today() formula

If it is a formula then it won't trigger unless it is a calculate event but is it a formula as in your other thread you have been asking for a macro to put in the date as a value? If that is the case then the code should be in the same macro as the date inserting macro.