Solved: Data Change

[drums4monty]

Hi

I have a work book with 62 sheets, each sheet is named after a club. In cell B21 is the word Members, in B22 the word Staff and in B23 the word Red. There is a price in cells I21, I22 & I23. I need to update these prices which I could do but not all of the sheets have all of the data, for example sheet 1 has Members & Staff, but sheet 2 has Members, Staff & Red. Is there a way to update I21, I22 & I23 with the new data, which is ?6.14, ?0.38 & ?34.30, but only if that club requires the data? All clubs require the first 2 new prices and only certain ones require the price for Red.

[Bob Phillips]

Straight off of the top

[vba]

For Each sh In Activeworkbook.Worksheets
sh.Range("I21").Value = 6.14
sh.Range("I22").Value = 0.38
If sh.Range("B23").Value = "Red" Then sh.Range("I23").Value = 34.3
Next sh
[/vba]

[drums4monty]

Thanks xld that works great.

How would you use this to perform the same task on a workbook with e.g. 50 worksheets but only want to perform the task on sheets 5 to 30?

[Bob Phillips]

Are the ones to be ignored the first 4, or do you want to exclude by name? Are there any more after the 30th?

[drums4monty]

To be exact, I want to run the code on my workbook of 69 worksheets. The code needs to run on sheets 2 to 63 inclusive, so it misses the first sheet and the last 6.

The code works fine, it just puts the data on all the sheets that I then manually delete which is not a problem, I was just wondering if it is possible to make it work only on the sheets that need the data.

[Bob Phillips]

[vba]
For i = 2 To 63
Set sh = Worksheets(i)
sh.Range("I21").Value = 6.14
sh.Range("I22").Value = 0.38
If sh.Range("B23").Value = "Red" Then sh.Range("I23").Value = 34.3
Next i
[/vba]

[drums4monty]

Thanks xld that works a treat.