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
Try Destination = wb.Worksheets(Diameter)
User contributions licensed under CC BY-SA 3.0