Excel 2007 Formatting VB Code Needed

I've created a macro-enabled workbook that will split out and parse your data with one table per sheet.  It is highly sensitive to this particular layout, meaning if there are any significant changes to the header, footer, or question values then this macro may not work.

Here's the code:

Option Explicit
Sub Split_Tables_To_Sheets()
    Dim rng As Range
    Dim cl As Object
    Dim strRawSh, strNewSh As String
    Dim arrSheetList() As String
    Dim strHeader(5) As String
    Dim boolScores As Boolean
    Dim arrScores(200, 5) As Variant
    Dim intLR, r, x, c, sh As Integer
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        
    strRawSh = Sheets(1).Name
    Sheets(strRawSh).Select
    intLR = Range("A" & Cells.Rows.Count).End(xlUp).Row
    Do Until Left(Cells(intLR, 1), 5) = "Note:"
        intLR = intLR - 1
    Loop
    Set rng = Range("A2:A" & intLR)
    boolScores = False
    r = 0
    
    For Each cl In rng
        If cl.Offset(0, 1).Value = "Question:" Then 'new question/sheet
            strNewSh = Trim("Q_" & _
                Left(Trim(cl.Offset(0, 2).Text), _
                InStr(1, Trim(cl.Offset(0, 2).Text), " ", vbTextCompare)))
            
            'Test for existing sheet and delete
            On Error Resume Next
            Sheets(strNewSh).Delete
        
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = strNewSh
            Sheets(strRawSh).Activate
        End If
        If Left(cl.Value, 5) = "Note:" Then 'end of table copy to new sheet
            boolScores = False
            With Sheets(strNewSh)
                For c = 0 To 5
                    .Cells(1, c + 1).Value = strHeader(c)
                Next c
                For x = 1 To r
                    For c = 0 To 5
                        .Cells(x + 1, c + 1).Value = arrScores(x, c)
                    Next c
                Next x
                .Columns("A:F").EntireColumn.AutoFit
            End With
            Erase arrScores
            r = 0
        End If
        If boolScores Then 'collect score values in array
            r = r + 1
            arrScores(r, 0) = cl.Value
            arrScores(r, 1) = cl.Offset(0, 1).Value
            arrScores(r, 2) = cl.Offset(0, 3).Value
            arrScores(r, 3) = cl.Offset(0, 5).Value
            arrScores(r, 4) = cl.Offset(0, 8).Value
            arrScores(r, 5) = cl.Offset(0, 9).Value
        End If
        If cl.Value = "Evaluator Name" Then 'header row
            strHeader(0) = cl.Value
            strHeader(1) = cl.Offset(0, 1).Value
            strHeader(2) = cl.Offset(0, 2).Value
            strHeader(3) = cl.Offset(0, 5).Value
            strHeader(4) = cl.Offset(0, 7).Value
            strHeader(5) = cl.Offset(0, 9).Value
            boolScores = True
        End If
    Next cl
    
    Sheets(strRawSh).Select
    Range("A1").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox "Data moved to new sheets"

End Sub

Select allOpen in new window

To run, press [Alt]+[F8] to call up the Macro dialog box then run "Split_Tables_To_Sheets".

Regards,
-Glenn
EE-Example-July-2014.xlsm

Glenn - This appears to be working great.  My client threw me a curve ball on the data set though.  I had requested the unmodified data report and apparently what I received was not the actual report.  If you look at the example workbook provided you'll see in cell B2 a form name (  SA Oct 2013 v1 DEV).  When the system generates this report the actual raw data has two form reports on the same raw data worksheet.  The first one as indicated and the second one has 'DEV' in the text (  SA Oct 2013 v1 DEV).  The section with the DEV has the additional text after the 'Total' in column A.

Is it possible to update the code so that it first identifies the form name in B2, breaks everything out as you've provided and stops when it sees the DEV form text in column B, then breaks those additional sections out into individual tabs and stops once it sees the additional text after the 'Total' in column A?  

If this requires a great deal of rework I can approve the work submitted and repost so you can make the adjustments.  Just let me know.  Thanks so much for your assistance on putting this together!
EE-Example-July-2014.xlsx

So, you'll have two sets of tables, each with the same question number, but one "normal" and one "DEV"?

I thought I was being clever using the question number as a sheet name!  If I hadn't done that, you would be able to run this macro on the new data set without any issue.  But currently, the four tables in the DEV section have the same question numbers, so their sheets overwrite the first four.

So:

How do you want the new sheets to be named?

For example, they are "Q_3.a", "Q_3.b", and so on.  Do you want to see "Q_3.a.DEV" for the new tables or similar?

-Glenn

Q_3.a.DEV option would be perfect!

Give me about 15 mins and I'll whip up a modified workbook/macro for you.

Glenn - You are the man!  Turned out great and your efforts has created a lot of efficiency and improved quality for the final report.  Thanks again!  Cheers!

I'm glad I was able to help.

-Glenn