Help with autofill when looping

[RiciH83]

Hi i'm trying to loop the following code to do the same thing on all the worksheets.

When run it does the initial activesheet spot on but when it loops to the next it does everything except the autofill and i can't work out why

if somebody can help i'd appreciate it

Option Explicit

Sub loopingCode()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    
     Dim r As Long

r = Cells(Rows.Count, "B").End(xlUp).Row
On Error Resume Next
                   
  

With ws
 
    .Rows("1:2").Insert Shift:=xlDown
    .Range("AR3:AV3") = Array("Accrual", "Committed", "To Accrue Y/N", "Name", "To Post")
    .Range("AS3") = "Accrual"
    .Range("AR2") = "Accrual Date"
    .Range("AS2").FormulaR1C1 = "='[Finance Macro Vault.xls]Project Overview'!R4C13"
    .Range("AR4").FormulaR1C1 = "=IF(RC33<=R2C45,RC32,0)"
    .Range("AR4").AutoFill Destination:=Range("AR4:AR" & r)    :mad:
    
End With

                 
    Next ws
    
End Sub

[elleg]

As far as your question goes, I think your problem is that you're not specifiying the sheet in the destination range. If you don't specify the sheet, it's going to use your active sheet each time. So, you can either activate each sheet as you loop (example 1), or you can specify the sheet in the destination range (example 2). (Since you're using a 'with' statement, to specify the sheet in the destination range, you just have to add a period before the range - .Range("MyRange"), etc.).

example 1:

ws.Activate

With ws
 
    .Rows("1:2").Insert Shift:=xlDown
    .Range("AR3:AV3") = Array("Accrual", "Committed", "To Accrue Y/N", "Name", "To Post")
    .Range("AS3") = "Accrual"
    .Range("AR2") = "Accrual Date"
    .Range("AS2").FormulaR1C1 = "='[Finance Macro Vault.xls]Project Overview'!R4C13"
    .Range("AR4").FormulaR1C1 = "=IF(RC33<=R2C45,RC32,0)"
    .Range("AR4").AutoFill Destination:=Range("AR4:AR" & r)    
    
End With

example 2:

With ws
 
    .Rows("1:2").Insert Shift:=xlDown
    .Range("AR3:AV3") = Array("Accrual", "Committed", "To Accrue Y/N", "Name", "To Post")
    .Range("AS3") = "Accrual"
    .Range("AR2") = "Accrual Date"
    .Range("AS2").FormulaR1C1 = "='[Finance Macro Vault.xls]Project Overview'!R4C13"
    .Range("AR4").FormulaR1C1 = "=IF(RC33<=R2C45,RC32,0)"
    .Range("AR4").AutoFill Destination:=.Range("AR4:AR" & r)
    
End With

Something I noticed aside from your question: Right now, your code fills in Array("Accrual", "Committed", "To Accrue Y/N", "Name", "To Post") in cells AR3 to AV3, then it immediately replaces 'Committed' with 'Accrual', so cells AR3 to AV3 now say: "Accrual", "Accrual", "To Accrue Y/N", "Name", "To Post". Did you actually want that to say accrual twice? If not, I'd take out .Range("AS3") = "Accrual" from your code.

Additionally, you have declared variables throughout your code. I would recommend keeping your variable declarations grouped at the top of the procedure. It's proper coding practice, and for a good reason, it makes things easier when you have to go back through it at a later date, or as you're debugging. I would do something like the following to keep your code neat:

Option Explicit
'PUBLIC/CONSTANT VARIABLE DECLARATIONS------------------------------------------------------

'PROCEDURES----------------------------------------------------------------------------------
Sub loopingCode()
'---PROCEDURE DESCRIPTION/COMMENTS-----------------------------------------------------------
    'This procedure loops through all worksheets in a workbook and ... <description here>
    '
'---VARIABLE DECLARATIONS--------------------------------------------------------------------
    Dim ws As Worksheet
    Dim r As Long

'---CODE-------------------------------------------------------------------------------------
    For Each ws In ActiveWorkbook.Worksheets

        r = Cells(Rows.Count, "B").End(xlUp).Row
        On Error Resume Next
        
        With ws
            .Rows("1:2").Insert Shift:=xlDown
            .Range("AR3:AV3") = Array("Accrual", "Committed", "To Accrue Y/N", "Name", "To Post")
            .Range("AS3") = "Accrual" 'did you actually want this here?
            .Range("AR2") = "Accrual Date"
            .Range("AS2").FormulaR1C1 = "='[Finance Macro Vault.xls]Project Overview'!R4C13"
            .Range("AR4").FormulaR1C1 = "=IF(RC33<=R2C45,RC32,0)"
            .Range("AR4").AutoFill Destination:=Range("AR4:AR" & r)
        End With

    Next ws
    
End Sub

[elleg]

Actually, looking at the code one more time, I do think you need to activate the workbook in each loop, but you need to do it before you set the variable r's value. Right now, you're saving the last row of the active sheet in r, but you're not changing the active sheet. I'm assuming that you want to get the last row of column B for each sheet you're putting the headers/formulas on.

[Hack]

Take out the On Error Resume Next (and throw it away)

You might be generating an error and you don't know it.

If you don't know about the error, you can't fix it.

[RiciH83]

i was literally just typing to ask that,

here's where i'm at and now just need to know how for it to calculate the rows per sheet?

ption Explicit

Sub loopingCode()
    'Formats all worksheets to show accruals/commited spend
    
    Dim ws As Worksheet
    Dim r As Long
    
    For Each ws In ActiveWorkbook.Worksheets
    
        r = Cells(Rows.Count, "B").End(xlUp).Row
                 
  

With ws
    .Rows("1:2").Insert Shift:=xlDown
    .Range("AR3:AV3") = Array("Accrual", "Committed", "To Accrue Y/N", "Name", "To Post")
    .Range("AR2") = "Accrual Date"
    .Range("AS2").FormulaR1C1 = "='[Finance Macro Vault.xls]Project Overview'!R4C13"
    .Range("AR4").FormulaR1C1 = "=IF(RC33<=R2C45,RC32,0)"
    .Range("AR4").AutoFill Destination:=.Range("AR4:AR" & r)
    .Range("AS4").FormulaR1C1 = "=IF(RC33>R2C45,RC32,0)"
    .Range("AS4").AutoFill Destination:=Range("AS4:AS" & r)
    .Range("AS4").FormulaR1C1 = "=IF(RC33>R2C45,RC32,0)"
    .Range("AR4").FormulaR1C1 = "=IF(RC33<=R2C45,RC32,0)"
    .Range("AT4").FormulaR1C1 = "Y"
    .Range("AT4").AutoFill Destination:=Range("AT4:AT" & r)
    .Range("AV4").Formula = "=IF((RC46=""Y""),RC44,0)"
    .Range("AV4").AutoFill Destination:=Range("AV4:AV" & r)
    .Range("AR4:AV1040").FormatConditions.Delete
    .Range("AR4:AV1040").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="0"
    .Range("AR4:AV1040").FormatConditions(1).Font.ColorIndex = 2
    .Range("AR4:AV1040").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""Y"""
    .Range("AR4:AV1040").FormatConditions(2).Font.ColorIndex = 2

    
End With

                 
    Next ws
    
End Sub