Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,

share|improve this question
    
Thanks all. I was overthinking how to tackle this big time! I appreciate all the answers – rencjam Mar 10 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 at 21:27
up vote 4 down vote accepted

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.

enter image description here

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.

share|improve this answer

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
share|improve this answer
    
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 at 21:12
    
@rencjam You would Select an array of Sheets that does not include the first two Sheets. – Gary's Student Mar 10 at 21:16
    
@rencjam See my EDIT#1 – Gary's Student Mar 10 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
share|improve this answer
    
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 at 21:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .