Help with VBA code

[OliviaT]

Hi,

I need help with the vba code below. The code works but only on the current worksheet that I select. It won't loop through all the remaining worksheets in my workbook? I do NOT have a problem with the formulas. I want the formulas (Date and Sumif) to be copied to the other worksheets (currently 50) excluding the 9 mentioned below. I have tried to search the forum but can't seem to find a solution. The exact number of worksheets in my workbooks will vary each month.

I am new to vba. The code below was piecemeal-ed from some other code that I have founded to work but I inserted my new formulas in there instead of the original formula.

Could someone please let me know how to get my formulas copied to the other worksheets? Thank you in advance for your time and assistance.




Sub Macro2()
je = "JE"
qsumttm = "QSumTTM"
dttm = "DetailTTM"
qsum = "QSum"
d = "Detail"
wttm = "WalTTM"
w = "Wal"
staff = "Stafflisting"
Sum = "Summary"

Application.Calculation = xlCalculationManual
Dim Sht As Worksheet

For Each Sht In ActiveWorkbook.Sheets
With Sht
If Sht.Name <> je And Sht.Name <> qsumttm And Sht.Name <> dttm And Sht.Name <> qsum And Sht.Name <> d And Sht.Name <> wttm And Sht.Name <> w And Sht.Name <> staff And Sht.Name <> Sum Then

Range("S10").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),1,1)"
Range("S11").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R10C5:R10C16,"">=""&R10C19,RC[-14]:RC[-3])"
Selection.AutoFill Destination:=Range("S11:S135"), Type:=xlFillDefault

End If

End With
Next Sht
Application.Calculation = xlCalculationAutomatic
End Sub

 

[chika235]

Not sure if this helps but try again if you take off with sht and end with.
All your sheet names should be in "", i.e "je", "qsum",...

 

[OliviaT]

Hi chika235,

Thanks for the response! Sorry, but I don't understand what you are asking me to do. I went ahead and removed the exception sheet code but still it won't loop nor copy the formulas to the other worksheet. Below is the new code. Any other suggestions? Thanks again.

Sub Macro2()

Application.Calculation = xlCalculationManual
Dim Sht As Worksheet

For Each Sht In ActiveWorkbook.Sheets
With Sht

Range("S10").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),1,1)"
Range("S11").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R10C5:R10C16,"">=""&R10C19,RC[-14]:RC[-3])"
Selection.AutoFill Destination:=Range("S11:S135"), Type:=xlFillDefault



End With
Next Sht
Application.Calculation = xlCalculationAutomatic
End Sub

 

[chika235]

No, it was my fault! Sorry was on my mobile so could not respond properly. Try the below and see how you go.

Sub Macro2()
je = "JE"
qsumttm = "QSumTTM"
dttm = "DetailTTM"
qsum = "QSum"
d = "Detail"
wttm = "WalTTM"
w = "Wal"
staff = "Stafflisting"
Sum = "Summary"

Application.Calculation = xlCalculationManual
Dim Sht As Worksheet

For Each Sht In ActiveWorkbook.Sheets

If Sht.Name <> "je" And Sht.Name <> "qsumttm" And Sht.Name <> "dttm" And Sht.Name <> "qsum" And Sht.Name <> "d" And Sht.Name <> "wttm" And Sht.Name <> "w" And Sht.Name <> "staff" And Sht.Name <> "Sum" Then

Range("S10").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),1,1)"
Range("S11").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R10C5:R10C16,"">=""&R10C19,RC[-14]:RC[-3])"
Selection.AutoFill Destination:=Range("S11:S135"), Type:=xlFillDefault

End If

Next Sht
Application.Calculation = xlCalculationAutomatic
End Sub

 

[sericom]

Sub Macro2()
    Application.Calculation = xlCalculationManual
    Dim Sht As Worksheet

    For Each Sht In ActiveWorkbook.Sheets
        With Sht
            .Range("S10").FormulaR1C1 = "=DATE(YEAR(RC[-3]),1,1)"
            .Range("S11").FormulaR1C1 = "=SUMIF(R10C5:R10C16,"">=""&R10C19,RC[-14]:RC[-3])"
            .Range("S11").AutoFill Destination:=.Range("S11:S135"), Type:=xlFillDefault
        End With
    Next Sht
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[OliviaT]

Hi Chika235,

It didn't work. I removed the IF function just to see if it would loop through all the worksheets in my workbooks but it didn't. Thanks for trying and for your time.

 

[sericom]

With sheet exclusions

Sub Macro2()
    Application.Calculation = xlCalculationManual
    Dim Sht As Worksheet

    For Each Sht In ActiveWorkbook.Sheets
        Select Case Sht.Name
        Case "JE", "QSumTTM", "DetailTTM", "QSum", "Detail", "WalTTM", "Wal", "Stafflisting", "Summary"
        Case Else
            With Sht
                .Range("S10").FormulaR1C1 = "=DATE(YEAR(RC[-3]),1,1)"
                .Range("S11").FormulaR1C1 = "=SUMIF(R10C5:R10C16,"">=""&R10C19,RC[-14]:RC[-3])"
                .Range("S11").AutoFill Destination:=.Range("S11:S135"), Type:=xlFillDefault
            End With
        End Select
    Next Sht
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[OliviaT]

Hi Sericom,

Thank you so much for the code! It worked. However, I have one slight issue. How do I exclude the 9 worksheets in my original code?

 

[chika235]

I again overlooked the declaration you already had at the beginning of the code. Take back one step I hope 3rd time lucky.

[COLOR=#333333][COLOR=#333333][I]Sub Macro2()[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]je = "JE"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]qsumttm = "QSumTTM"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]dttm = "DetailTTM"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]qsum = "QSum"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]d = "Detail"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]wttm = "WalTTM"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]w = "Wal"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]staff = "Stafflisting"[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]Sum = "Summary"[/I][/COLOR][/COLOR]

[COLOR=#333333][COLOR=#333333][I]Application.Calculation = xlCalculationManual[/I][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][I]Dim Sht As Worksheet[/I][/COLOR][/COLOR]

[COLOR=#333333][COLOR=#333333][I]For Each Sht In ActiveWorkbook.Sheets[/I][/COLOR][/COLOR]

[COLOR=#333333][COLOR=#333333][I]If Sht.Name <> je And Sht.Name <> qsumttm And Sht.Name <> dttm And Sht.Name <> qsum And Sht.Name <> d And Sht.Name <> wttm And Sht.Name <> w And Sht.Name <> staff And Sht.Name <> Sum Then[/I][/COLOR][/COLOR]

[I][B]Range("S10").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),1,1)"
Range("S11").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R10C5:R10C16,"">=""&R10C19,RC[-14]:RC[-3])"
Selection.AutoFill Destination:=Range("S11:S135"), Type:=xlFillDefault[/B]

End If

Next Sht
Application.Calculation = xlCalculationAutomatic
[/I]
[I]End Sub[/I]

 

[OliviaT]

Sericom,

Thank you so much!!! That worked perfectly. Truly appreciated your help!