Run Code If Worksheet Exists

**heliskier89** · 05-27-2011, 01:42 PM

Does anyone know how best to acheive the following? I guess it would have to be done with coding.

I need to insert a formula into a particular cell if the worksheet is contained within the workbook. I've attached a workbook to aid my explanation.

For example, in my workbook you will see that there are only 4 worksheets, namely 'Master', '20', '40' & '60'. The Master worksheet will always be present but there may be more or less each time this workbook is created.

The routine would need to check what worksheets were present, with the exception of 'Master' and insert the following formula into the associated cell.

Please Login or Register  to view this content.

The routine would then continue to input formulas for the remaining worksheets. The formula and cell will obviously change for each worksheet. For example, '40' would be

Please Login or Register  to view this content.

If the worksheet(s) were not included in the workbook then "Not Required" would be inserted into the corressonding cell on the 'Master' worksheet.

In short,
Check which worksheets exist.
Insert formula into associated cell on 'Master' if worksheet exists.
Insert text "Not Required" if worksheet doesn't exist.

Any advice gratefully received.

**Whizbang** · 05-27-2011, 02:17 PM

Why not just do:

=IFERROR(IF(COUNTA(20!R[-4]C[1]:R[18]C[2])=ROWS(20!R[-4]C[1]:R[18]C[2])*COLUMNS(20!R[-4]C[1]:R[18]C[2]),"Complete","In Progress"),"Not Required")

No code required.

Edit*****
Whoa. Craziness. Evidently COLUMNS() and ROWS() will return the number of columns/rows even if the sheet doesn't exist.

Hmmm... Let me work on this.

**Whizbang** · 05-27-2011, 02:27 PM

Alright... What about this?

=IF(ISREF('20'!A1),"Not Required",IF(COUNTA('20'!D5:E27)=ROWS('20'!D5:E27)*COLUMNS('20'!D5:E27),"Complete","In Progress"))

**heliskier89** · 05-27-2011, 04:47 PM

Apologies. I forgot to attach the workbook as stated in my original thread. I've now.done this.

Whizbang, I tried to put your code in but came up as a syntax error in VBA.

**Whizbang** · 05-27-2011, 04:57 PM

I meant to avoid the use of VBA altogether. Just enter that formula directly into the cell. When you enter it, it will pop up a broswer box. Just hit cancel.

**heliskier89** · 05-28-2011, 01:03 PM

Thanks for your efforts Whizbang, I tried entering your formula into the cell but all that happened was that "Not Required" appeared in the cell! For the workbook I've attached as an example then "In Progress" should have appeared.

In any case I need to acheive this automatically, entering formula manually into each cell is not an option unfortunately.

As stated in an earlier post, this is a brief outline of the automated routine I require:
"Check which worksheets exist.
Insert formula into associated cell on 'Master' if worksheet exists.
Insert text "Not Required" if worksheet doesn't exist."

Therefore for attached workbook "Book 1";
The routine would check which worksheets exist = 20, 40 & 60. The Master is omitted.
The associated formula 20, 40 & 60 would be added to cells C5, C9 & C13 respectively.
Worksheets 10, 30, 50, 70 & 80 don't exist therefore the text "Not Required" would be added to these cells.

Hopefully, I've clarified the requirements.

**JBeaucaire** · 05-28-2011, 10:45 PM

This should do it:

Please Login or Register  to view this content.

NOTE: The resulting formula placed in C5 and C9 do NOT have the same evaluation range on those sheets. C5 is evaluating to range D1:E23 and C9 is evaluating D5:E27. If you need them all to reference D1:E23 we can fix that.

Please Login or Register  to view this content.

**heliskier89** · 05-29-2011, 04:35 PM

JBeaucaire you are a genius. Both codes worked well but the second is the one that fits my needs best due to the reference cells for the formula.

Is there any chance that you could add a few lines of comment to your code explaining what's happening at each step? The reason I ask is that I like to understand how the code works and since my knowledge is limited I can't quite follow each part all the time.

Thanks again so much. Your assistance is very much appreciated.

**JBeaucaire** · 05-30-2011, 04:55 AM

Comments added to code #2 in the post above.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.