[Solved] Help creating a Summary Page from user-defined worksheets

**gophil1** · 08-15-2012, 07:49 PM

I have the following code that adds all of the values of column BD (cells BD8:BD100) from various worksheets (user defined) and totals the results onto a Summary page column C (cells C8:C100).

Please Login or Register  to view this content.

The problem occurs when Sheet(4) doesn't exist.
A Run-time error '9': Subscript out of range error results when the user attempts to run a summary report from the Summary tab when no Sheet4 exists. The following line of code is highlighted in yellow: ws2 = Sheets(4).Name

Is there a way to rewrite the code so that a message box appears directing the user to create a worksheet from the location tab and enter the respective quantities if a location worksheet "Sheet(4)" hasn't been created or possibly check to see if "Sheet (4)" exists and if it doesn't direct the user to create a worksheet from the location tab?

I hope I'm making sense or that the attached file better illustrates what I'm attempting.
Thanks in advance for any assistance provided.

**rylo** · 08-15-2012, 08:06 PM

Hi

Bit brutal but hopefully you will get what I mean.

Please Login or Register  to view this content.

rylo

**gophil1** · 08-17-2012, 12:45 PM

Thanks for the help, but for some reason the code isn't generating a summary page total now.
The extra bit of code that you added seems pretty straight forward and I'm at a loss as to why it doesn't work.

**gophil1** · 08-17-2012, 12:57 PM

I think I may have spoke too soon. It looks like the Summary tab always has to be the third tab in the workbook in order for the coding to work properly.
I'm marking this as "Solved".
Thanks for your help.