Auto-filling formulas across multiple sheets

**kestefon** · 02-20-2013, 03:56 PM

Hi, everyone,

I have a massive document with 1000+ sheets. Each sheet has "year" columns (2009, 2010, 2011, and 2012), and varying numbers of rows (anywhere from 1 to about 100), and I'd like to add columns to each sheet showing the year vs. year % difference.

The attached Excel document has a before/after example. Any VBA guidance here would be greatly appreciated.

Excel Percent Diff Example.xlsx Excel Percent Diff Example.xlsx

**JBeaucaire** · 02-20-2013, 08:35 PM

The BEFORE sheet already has empty columns between the columns, so all you need is help inserting the formulas?

This should do it:

Option Explicit

Sub AddFormulas()
Dim ws As Worksheet, LR As Long, col As Long

On Error Resume Next
For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    For col = 4 To 10 Step 3
        With ws.Cells(2, col).Resize(LR - 1)
            .NumberFormat = "0%"
            .FormulaR1C1 = "=RC[-1]/RC[2]-1"
        End With
    Next col
Next ws

End Sub