PDA

View Full Version : Solved: Data Change



drums4monty
03-05-2008, 03:26 AM
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
03-05-2008, 04:16 AM
Straight off of the top



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

drums4monty
03-05-2008, 06:42 AM
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
03-05-2008, 07:31 AM
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
03-05-2008, 08:25 AM
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
03-05-2008, 08:47 AM
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

drums4monty
03-05-2008, 09:21 AM
Thanks xld that works a treat.