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

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,

• Thanks all. I was overthinking how to tackle this big time! I appreciate all the answers – rencjam Mar 10 '16 at 21:22
• Also, if it were possible, I'd give both answers from ode2k and Gary's Student a check if it would let me. I chose to accept ode2k's answer because it was the answer I used in the end as it allowed me to bypass VBA all together. Thank you both @Gary's Student and ode2k. – rencjam Mar 10 '16 at 21:27

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.

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

• Looks like I was overthinking this. That worked great! Is there any way to modify this to exclude the first two worksheets? I was thinking of using an If command to do the job, but haven't gotten that to work for me yet. Thank you for the help – rencjam Mar 10 '16 at 21:12
• @rencjam You would Select an array of Sheets that does not include the first two Sheets. – Gary's Student Mar 10 '16 at 21:16
• @rencjam See my EDIT#1 – Gary's Student Mar 10 '16 at 21:42

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

• I've tried your modified code and it still does not advance to the next sheet. Also, when you say i'm not specifying which range i'd like to use, do you mean using "ws.range("D7")? – rencjam Mar 10 '16 at 21:18