[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).

Sub CopyToSummary()
Dim ws2 As String, wsLast As String

    ws2 = Sheets(4).Name
    wsLast = Sheets(Sheets.Count).Name
    
    With Sheets(3)
        .Range("c8:c100").Formula = "=SUM('" & ws2 & ":" & wsLast & "'!bd8)"
    End With
End Sub

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.

Sub CopyToSummary()
  Dim ws2 As String, wsLast As String
  If Sheets.Count > 3 Then
    ws2 = Sheets(4).Name
    wsLast = Sheets(Sheets.Count).Name
    
    With Sheets(3)
        .Range("c8:c100").Formula = "=SUM('" & ws2 & ":" & wsLast & "'!bd8)"
    End With
  Else
    MsgBox "build Sheet1 before proceeding"
  End If
End Sub

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.