Run-time error '9': Subscript out of range ERROR

[programsam]

Hey gang,

Getting a run-time error with this code. Attempting to create separate worksheets and copy rows based on data in column "B" but it's getting hung up on this line.

Set tws = Sheets(cel.Value)

When I look at values, all I get is tws = Nothing.

Here's the whole code.

Sub splitData()
     
     Dim sws As Worksheet
     Dim tws As Worksheet
     Dim cel As Range
     
     Set sws = Sheets("Summary for Export")
     
     For Each cel In sws.Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
         Set tws = Sheets(cel.Value)
         cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
     Next cel
     
 End Sub

 

[mikerickson]

It looks like cel doesn't contain a sheet's name. Perhaps extra or missing spaces.

 

[programsam]

Hi Mike, when I check the value... it shows as 1280 which is the value of the first row (B2) but it does not create a worksheet or add the row to the worksheet, nor does it move to the next row and add that value. Thoughts?

It looks like cel doesn't contain a sheet's name. Perhaps extra or missing spaces.

 

[mikerickson]

Do you have a sheet named "1280"?
If not, you need to create code to create it. There is no sheet creation code in that routine.
This code adds an error handler to create a worksheet if one doesn't already exist.
(There is no handling for the situation where a cell contains an illegal sheet name.)

Sub splitData2()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim cel As Range
    
    Set sws = Sheets("Summary for Export")
    
    For Each cel In sws.Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        On Error GoTo MakeSheet
        Set tws = Sheets(cel.Value)
        On Error GoTo 0
        cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next cel
    
Exit Sub
MakeSheet:
    If Err = 9 Then
        Err.Clear
        With cel.Parent.Parent
            .Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = CStr(cel.Value)
        End With
        Resume
    Else
        MsgBox "Error #" & Err & Error
        Err.Clear
    End If
End Sub

 

[programsam]

Okay, now I've figured out why it didn't work.

It looks like the first three rows have data "1280" in column B so what's happening is the first row gets hit, the page is created and then VBA gives me an error after the second one because of the multiple occurrences of the same data.

How could this be written to only create a page off of the FIRST occurrence of "1280" and then move to the next row with different data, say "1290"?

Thanks Mike, I feel like I'm developing an understanding of what's going on but this one has got me stumped.

 

[Norie]

Does this work?

Set tws = Sheets(CStr(cel.Value))

 

[programsam]

Indeed it does Norie, thanks brother!

Last question, how would I carry over headers to row one on these pages from the page the data was copied from>