How to shorten/clean-up this code?

[edgarh90]

I wrote some code to automatically update cell formulas based on the current month. I have 6 sheets in total (REPORT1- REPORT6) that need to be updated. The cells are different in each sheet. What would be the best way to optimize this code so I can make quick changes for each sheet?

Sub Month()
If CurrentMonth = July Then
Worksheets("REPORT1").Range("X9").Formula = "=SUM(R9:W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown
 
Worksheets("REPORT1").Range("X38").Formula = "=SUM(R38:W38)"
Worksheets("REPORT1").Range("X38:X41").FillDown
 
 
Worksheets("REPORT1").Range("X75").Formula = "=SUM(R75:W75)"
Worksheets("REPORT1").Range("X75:X97").FillDown
 
 
ElseIf CurrentMonth = August Then
Worksheets("REPORT1").Range("X9").Formula = "=SUM(S9:W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown
 
Worksheets("REPORT1").Range("X38").Formula = "=SUM(S38:W38)"
Worksheets("REPORT1").Range("X38:X41").FillDown
 
 
Worksheets("REPORT1").Range("X75").Formula = "=SUM(S75:W75)"
Worksheets("REPORT1").Range("X75:X97").FillDown
 
ElseIf CurrentMonth = September Then
Worksheets("REPORT1").Range("X9").Formula = "=SUM(T9:W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown
 
Worksheets("REPORT1").Range("X38").Formula = "=SUM(T38:W38)"
Worksheets("REPORT1").Range("X38:X41").FillDown
 
 
Worksheets("REPORT1").Range("X75").Formula = "=SUM(T75:W75)"
Worksheets("REPORT1").Range("X75:X97").FillDown
 
ElseIf CurrentMonth = October Then
Worksheets("REPORT1").Range("X9").Formula = "=SUM(U9:W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown
 
Worksheets("REPORT1").Range("X38").Formula = "=SUM(U38:W38)"
Worksheets("REPORT1").Range("X38:X41").FillDown
 
 
Worksheets("REPORT1").Range("X75").Formula = "=SUM(U75:W75)"
Worksheets("REPORT1").Range("X75:X97").FillDown
 
ElseIf CurrentMonth = November Then
Worksheets("REPORT1").Range("X9").Formula = "=SUM(V9:W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown
 
Worksheets("REPORT1").Range("X38").Formula = "=SUM(V38:W38)"
Worksheets("REPORT1").Range("X38:X41").FillDown
 
 
Worksheets("REPORT1").Range("X75").Formula = "=SUM(V75:W75)"
Worksheets("REPORT1").Range("X75:X97").FillDown
 
ElseIf CurrentMonth = December Then
Worksheets("REPORT1").Range("X9").Formula = "=SUM(W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown
 
Worksheets("REPORT1").Range("X38").Formula = "=SUM(W38)"
Worksheets("REPORT1").Range("X38:X41").FillDown
 
 
Worksheets("REPORT1").Range("X75").Formula = "=SUM(W75)"
Worksheets("REPORT1").Range("X75:X97").FillDown
 
End If
End Sub

 

[jonathann3891]

See if this will work. You may get you in the right direction.

LastRow = ActiveSheet.Cells(Rows.Count, "R").End(xlUp).Row
Range("X9:X" & LastRow).Formula = "=[COLOR=#333333]SUM(R9:W9)[/COLOR]"

 

[Joe4]

If you use R1C1 notation, you can get rid of all your FillDowns. The good news is if you use the Macro Recorder, and enter the formula in your first cell, it will give you the exact code that you need.

So a block like this:

Worksheets("REPORT1").Range("X9").Formula = "=SUM(R9:W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown

Can be replaced with this:

    Worksheets("REPORT1").Range("X9:X31").Formula = "=SUM(RC[-6]:RC[-1])"

Also, near the bottom, you have SUM formulas being applied to a single cell. There really isn't any point in summing a single cell. Just use the Cell reference.

So this:

Worksheets("REPORT1").Range("X9").Formula = "=SUM(W9)"
Worksheets("REPORT1").Range("X9:X31").FillDown

can be replaced with this:

Worksheets("REPORT1").Range("X9:X31").Formula = "=RC[-1]"

 

[micsza]

First I would use variables to refer to range addresses you wish to update, then I would use Select ... Case to set the variables accordingly to the month. That would reduce your code lines significantly. I would also look for some pattern (if there is any in the rest of your sheets but can't tell not seeing them) - e.g., here in REPORT1 you just need to offset +1 for each next month.

 

[Joe4]

I think I see what you might be after, though a lot of needed information is messing (like where does "CurrentMonth" come from, is that a global variable being set elsewhere?).

So, here is some code that will loop through all your sheets, and apply the proper formula based on the CurrentMonth:

Sub MonthMacro()

    Dim ws As Worksheet
    Dim os As Long
    
    Application.ScreenUpdating = False
    
    Select Case currentmonth
        Case "July"
            os = -6
        Case "August"
            os = -5
        Case "September"
            os = -4
        Case "October"
            os = -3
        Case "Novemeber"
            os = -2
        Case "December"
            os = -1
    End Select
    
'   Loop through each worksheet
    For Each ws In Worksheets
        ws.Range("X9:X31").Formula = "=SUM(RC[" & os & "]:RC[-1])"
        ws.Range("X38:X41").Formula = "=SUM(RC[" & os & "]:RC[-1])"
        ws.Range("X75:X97").Formula = "=SUM(RC[" & os & "]:RC[-1])"
    Next ws

    Application.ScreenUpdating = True
    
End Sub

Obviously, adjustments will need to be made to suit your situation.

 

[steve the fish]

YOu could try this:

Sub Month()
        
arr = Array("July", "August", "September", "October", "November", "December")
arr2 = Array(6, 5, 4, 3, 2, 1)

For i = LBound(arr) To UBound(arr)
    If LCase(arr(i)) = LCase(CurrentMonth) Then
        x = arr2(i)
    End If
Next
If Len(x) = 0 Then Exit Sub

For Each sh In ThisWorkbook.Sheets
    If LCase(Left(sh.Name, 6)) = "report" Then
        sh.Range("X9:X31").FormulaR1C1 = "=SUM(RC[-" & x & "]:RC[-1])"
        sh.Range("X38:X41").FormulaR1C1 = "=SUM(RC[-" & x & "]:RC[-1])"
        sh.Range("X75:X97").FormulaR1C1 = "=SUM(RC[-" & x & "]:RC[-1])"
    End If
Next

End Sub