Macro help Please

[julievandermeulen]

I have a workbook that creates a new sheet every week and is named by the date. Ex 11-11-2021, 11-18-2021, 11-25-2021, etc.

I need to write a macro that will:
Calculate the last 4 sheets
1. look at the names in cells C146:C233 and take the value in that row of column P146:P233 and add the number of the last 4 sheets and put the totals in V146:V233.
2. look at the names in cell C146:C233 and take the value in that row of column Q146:Q233 and add the number of the last 4 sheets and put the totals in W146:W233.
3. look at the names in cell C146:C233 and take the value in that row of column R146:R233 and add the number of the last for sheets and put the total in X146:X233
Calculate the year to date sheets (first sheet of the year through the most recent sheet)
1. look at the names in cells C146:C233 and take the value in that row of column P146:P233 and add the number of the YTD sheets and put the totals in AB146:AB233
2. look at the names in cell C146:C233 and take the value in that row of column Q146:Q233 and add the number of the YTD sheets and put the totals in AC146:AC233.
3. look at the names in cell C146:C233 and take the value in that row of column R146:R233 and add the number of the YTD sheets and put the total in AD146:AD233
Calculate the last 52 sheets
1. look at the names in cells C146:C233 and take the value in that row of column P146:P233 and add the number of the last 52 sheets and put the totals in AH146:AH233
2. look at the names in cell C146:C233 and take the value in that row of column Q146:Q233 and add the number of the last 52 sheets and put the totals in AI146:AI233.
3. look at the names in cell C146:C233 and take the value in that row of column R146:R233 and add the number of the last 52 sheets and put the total in AJ146:AJ233

So far I haven't written any of it, and am new to VBA so I need very detailed explanation. Or if you think I could do it with a formula that would be great as well.

 

[tonyyy]

You might consider the following...

Sub Kalculations()
Dim currentDate As String
Dim i As Long, j As Long
Dim wsExists As Boolean
Application.ScreenUpdating = False

'Monthly totals
currentDate = ActiveSheet.Name
For j = 1 To 4
    For i = 146 To 233
        ActiveSheet.Range("V" & i).Value = ActiveSheet.Range("V" & i).Value + Sheets(currentDate).Range("P" & i).Value
        ActiveSheet.Range("W" & i).Value = ActiveSheet.Range("W" & i).Value + Sheets(currentDate).Range("Q" & i).Value
        ActiveSheet.Range("X" & i).Value = ActiveSheet.Range("X" & i).Value + Sheets(currentDate).Range("R" & i).Value
    Next i
    currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")
Next j

'Year to date totals
currentDate = ActiveSheet.Name
Do Until Right(ActiveSheet.Name, 4) <> Right(currentDate, 4)
    For i = 146 To 233
        ActiveSheet.Range("AB" & i).Value = ActiveSheet.Range("AB" & i).Value + Sheets(currentDate).Range("P" & i).Value
        ActiveSheet.Range("AC" & i).Value = ActiveSheet.Range("AC" & i).Value + Sheets(currentDate).Range("Q" & i).Value
        ActiveSheet.Range("AD" & i).Value = ActiveSheet.Range("AD" & i).Value + Sheets(currentDate).Range("R" & i).Value
    Next i
    currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")
    wsExists = Evaluate("ISREF('" & currentDate & "'!A1)")
    If wsExists = False Then Exit Do
Loop

'Yearly totals
currentDate = ActiveSheet.Name
For j = 1 To 52
    For i = 146 To 233
        ActiveSheet.Range("AH" & i).Value = ActiveSheet.Range("AH" & i).Value + Sheets(currentDate).Range("P" & i).Value
        ActiveSheet.Range("AI" & i).Value = ActiveSheet.Range("AI" & i).Value + Sheets(currentDate).Range("Q" & i).Value
        ActiveSheet.Range("AJ" & i).Value = ActiveSheet.Range("AJ" & i).Value + Sheets(currentDate).Range("R" & i).Value
    Next i
    currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")
    wsExists = Evaluate("ISREF('" & currentDate & "'!A1)")
    If wsExists = False Then Exit For
Next j
Application.ScreenUpdating = True
End Sub

It's assumed that the active sheet has the most current data, and the totals will be placed in this sheet.

Cheers,

Tony

 

[julievandermeulen]

Thanks.

It doesn't look like that looks at the name in Column C then takes the value in P:R and calculates them. It looks like it just calculated P:R regardless of the name in Column C.

 

[tonyyy]

When you say "looks at the name" -- what do you mean? Should the name in Column C match a name elsewhere?

Perhaps if you posted some sample data it might clarify what you're requesting. (This forum uses the XL2BB utility for posting data.)

Thanks.

 

[julievandermeulen]

I mean to look at the name in column c and take the value of the rows for P:R from one sheet and add the value to the next sheet for the same name.
For example say



Sheet 11-24-2022 C187 has 'Westside Pizza' P-R has 0, 0, 0

C188 has 'Village Books' P-R has 0, 0, 10



But in the next sheet we had to change the order (I just reversed these 2 as an example, usually it's done just to add a new name alphabetically)



Sheet 12-1-2022 C187 has 'Village Books' P-R has 0, 0, 10

C188 'Westside Pizza' P-R has 5, 0, 0



When I run the macro is there a way for it to look at C and Take Values in P-R and calculate it correctly? So that in



Sheet 12-8-2021 C187 has 'Village Books' and Columns V-X have the correct Values 0, 0, 20

C188 has 'Westside Pizza' V-X has 5, 0, 0





I'm not sure how to upload a sample file on here

 

[tonyyy]

See if the following works for you...

Sub Kalculations2()
Dim currentDate As String
Dim i As Long, j As Long
Dim wsExists As Boolean
Application.ScreenUpdating = False

'Monthly totals
currentDate = ActiveSheet.Name
For j = 1 To 4
    For i = 146 To 233
        If IsError(Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0)) Then
            ' match not found
        Else
            ActiveSheet.Range("V" & i).Value = ActiveSheet.Range("V" & i).Value + _
                Application.Index(Sheets(currentDate).Range("P146:P233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
            ActiveSheet.Range("W" & i).Value = ActiveSheet.Range("W" & i).Value + _
                Application.Index(Sheets(currentDate).Range("Q146:Q233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
            ActiveSheet.Range("X" & i).Value = ActiveSheet.Range("X" & i).Value + _
                Application.Index(Sheets(currentDate).Range("R146:R233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
        End If
    Next i
    currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")
Next j

'Year to date totals
currentDate = ActiveSheet.Name
Do Until Right(ActiveSheet.Name, 4) <> Right(currentDate, 4)
    For i = 146 To 233
        If IsError(Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0)) Then
            ' match not found
        Else
            ActiveSheet.Range("AB" & i).Value = ActiveSheet.Range("AB" & i).Value + _
                Application.Index(Sheets(currentDate).Range("P146:P233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
            ActiveSheet.Range("AC" & i).Value = ActiveSheet.Range("AC" & i).Value + _
                Application.Index(Sheets(currentDate).Range("Q146:Q233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
            ActiveSheet.Range("AD" & i).Value = ActiveSheet.Range("AD" & i).Value + _
                Application.Index(Sheets(currentDate).Range("R146:R233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
        End If
    Next i
    currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")
    wsExists = Evaluate("ISREF('" & currentDate & "'!A1)")
    If wsExists = False Then Exit Do
Loop

'Yearly totals
currentDate = ActiveSheet.Name
For j = 1 To 52
    For i = 146 To 233
        If IsError(Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0)) Then
            ' match not found
        Else
            ActiveSheet.Range("AH" & i).Value = ActiveSheet.Range("AH" & i).Value + _
                Application.Index(Sheets(currentDate).Range("P146:P233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
            ActiveSheet.Range("AI" & i).Value = ActiveSheet.Range("AI" & i).Value + _
                Application.Index(Sheets(currentDate).Range("Q146:Q233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
            ActiveSheet.Range("AJ" & i).Value = ActiveSheet.Range("AJ" & i).Value + _
                Application.Index(Sheets(currentDate).Range("R146:R233"), _
                Application.Match(ActiveSheet.Range("C" & i).Value, Sheets(currentDate).Range("C146:C233"), 0))
        End If
    Next i
    currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")
    wsExists = Evaluate("ISREF('" & currentDate & "'!A1)")
    If wsExists = False Then Exit For
Next j
Application.ScreenUpdating = True
End Sub

 

[julievandermeulen]

When I try to run the macro I get a

 

[Rick Rothstein]

Do you Dim your variables? If so, how is currentDate declared? Also, since you are setting currentDate equal to the name of the ActiveSheet and then using it to create a worksheet reference... why are you not simply using ActiveSheet directly? My guess is your problem is in Sheets(currentDate) and I'm guessing the problem will go away if you change each occurrence of it to ActiveSheet.

 

[tonyyy]

Rick - Thanks for stepping in. currentDate is declared as a String variable, and is re-set after each row of calculations using the statement:

currentDate = Format(CDate(currentDate) - 7, "mm-dd-yyyy")

This is used to step to the previous week sheet and continue the calculations.
That said, the IsError function is clearly not working in this circumstance (and can be re-written using an On Error Resume Next / On Error GoTo 0 method.) Before that, however...

julievandermeulen - In your initial post, you described the sheet names as "Ex 11-11-2021, 11-18-2021, 11-25-2021, etc." I assumed the pattern would be a new sheet added every 7 days. Is that the case? If not, the code will stop and generate a "Subscript out of range" error.

 

[julievandermeulen]

The sheets do add 7 days every week and I still get this error!