Split data into different sheets using a row value RRS feed

  • Question

  • Hello,

    I have data files with over 35,000 rows and 38 different columns. I want each Mode data to be in a separate sheet. Sometimes I have 20 modes, sometimes it's 30. I tried using the code below but I get an error "Type mismatch" in the bolded text below. Can someone please explain what I am doing wrong? 34 is the column number for CurrentMode

    Sub SplitData()
        Const NameCol = "CurrentMode"
        Const HeaderRow = 34
        Const FirstRow = 4
        Dim SrcSheet As Worksheet
        Dim TrgSheet As Worksheet
        Dim SrcRow As Long
        Dim LastRow As Long
        Dim TrgRow As Long
        Dim Student As String
        Application.ScreenUpdating = False
        Set SrcSheet = ActiveSheet
        LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row
        For SrcRow = FirstRow To LastRow
            Student = SrcSheet.Cells(SrcRow, NameCol).Value
            Set TrgSheet = Nothing
            On Error Resume Next
            Set TrgSheet = Worksheets(Student)
            On Error GoTo 0
            If TrgSheet Is Nothing Then
                Set TrgSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                TrgSheet.Name = Student
                SrcSheet.Rows(HeaderRow).Copy Destination:=TrgSheet.Rows(HeaderRow)
            End If
            TrgRow = TrgSheet.Cells(TrgSheet.Rows.Count, NameCol).End(xlUp).Row + 1
            SrcSheet.Rows(SrcRow).Copy Destination:=TrgSheet.Rows(TrgRow)
        Next SrcRow
        Application.ScreenUpdating = True
    End Sub

    Thursday, January 10, 2019 10:22 PM

All replies

  • NameCol should be the numberof the column on which you want to split. If that is the 34th column, you should use

        Const NameCol = 34

    The line

        Const HeaderRow = 34

    specifies which row contains the field names / column headers. Is that really row 34? Since you specify 4 as first data row, I would expect HeaderRow to be 3 or less.

    Regards, Hans Vogelaar (

    Thursday, January 10, 2019 10:30 PM
  • LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row

    The above line expects either a column letter or column index for the column in which you are trying to find the last row used. But you are using NameCol here which is a Const String "CurrentMode" and that's why it is producing the Type Mismatch Error.

    If the CurrentMode is the column header and you are not sure which column contains this header, first find out the column index of that column using Application.Match and then use the returned column index in place of NameCol in the line producing the error.

    Friday, January 11, 2019 2:46 AM
  • Please see the link below.

    That will tell you how to do what you wish to do.

    Saturday, January 26, 2019 6:59 PM