Split Worksheets By Criteria VB.Net

-1

I want to split worksheets with Diameter Criteria in Column C in a Master Sheet, My code is

        Private Sub Splitter()
        Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim Source As Excel.Worksheet
        Dim Destination As Excel.Worksheet
        Dim SourceRow As Long
        Dim Lastrow As Long
        Dim DestinationRow As Long
        Dim Diameter As String
        xl.Application.ScreenUpdating = False
        wb = xl.Workbooks.Open("E:\Patches\Main_Master_VB.xlsm")
        Source = wb.Worksheets("Master")
        Lastrow = Source.Cells(Source.Rows.Count, "C").End(Excel.XlDirection.xlUp).Row
        For SourceRow = 2 To Lastrow
            Diameter = Source.Cells(SourceRow, "C").Value
            Destination = Nothing
            On Error Resume Next
            Destination = wb.Sheets(Diameter.ToString)
            On Error GoTo 0
            If Destination Is Nothing Then
                Destination = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
                Destination.Name = Diameter
                Source.Rows(1).Copy(Destination.Rows(1))
            End If
            DestinationRow = Destination.Cells(Destination.Rows.Count, "C").End(Excel.XlDirection.xlUp).Row + 1
            Source.Rows(SourceRow).Copy(Destination:=Destination.Rows(DestinationRow))
        Next SourceRow
        xl.Application.ScreenUpdating = True
        End Sub

I receive error Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))' at the Line Destination = wb.Sheets(Diameter)

Appreciate your help

Thanks, Regards

excel
vb.net
split
interop
criteria
asked on Stack Overflow Jul 1, 2020 by Meho2016 • edited Jul 1, 2020 by braX

1 Answer

0

Try Destination = wb.Worksheets(Diameter)

answered on Stack Overflow Jul 1, 2020 by Joerg Wood

User contributions licensed under CC BY-SA 3.0