Insert a formula into the same cell on each worksheet using loop

Question

Using the loop function of VBA, I'm wanting to input the same formula into the same cell on each worksheet in my workbook.

I've tried the following code, but it doesn't loop through all worksheets. It only updates the one that happens to be selected by default:

Sub insertformula()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-5]C[-1],'SHEET ALL WILL REFERENCE'!C[-2]:C[-1],2,FALSE)"

Next

End Sub

What am I missing that's keeping this code from looping through all the worksheets?

Thank you,

Answer 1

You do not need to use VBA or loops to do this. If you are in your primary worksheet, control-click the sheet tabs one at a time until each of them are highlighted.

When all the sheets you want the formula to copy into are highlighted, you can type your formula in the cell and it will automatically copy into the exact cell on the respective worksheets. When you are done with that, make sure to click on an individual sheet to deselect the others.

Answer 2

You don't need to Loop. This will apply the formula to all sheets at the same time:

Sub hfdjs()
    Sheets.Select
    Range("D7").Activate
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-5]C[-1],'SHEET ALL WILL REFERENCE'!C[-2]:C[-1],2,FALSE)"
End Sub

EDIT#1:

Say we have 5 worksheets with default names and we want to jam a formula into all of them except the first two sheets.

Sub FormulaBlaster()
    Sheets(Array("Sheet3", "Sheet4", "Sheet5")).Select
    Range("B9").Select
    Selection.Formula = "=1+2"
End Sub

Answer 3

You're not specifying which range you'd like to use. You can also specify the formula for the range without selecting it first

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Range("D7").FormulaR1C1 = "=VLOOKUP(R[-5]C[-1],'SHEET ALL WILL REFERENCE'!C[-2]:C[-1],2,FALSE)"

Next