The code below works fine on its own but once i add option explicit at the start, the object variable or with block variable not set error appears at the sheetname = index3. I have seen other threads where the issue is solved by
set sheetname = ThisWorkbook.Worksheets("") but there are 3 sheets that will be created before i used this statement so i don't think it can work that way. Any ideas to solve this?
Option explicit Private Sub createvramp() Static count As Long Dim iRow As Long Dim aRow As Long Dim a As Long Dim b As Long Dim selectRange As Range Dim lastline As Integer Dim sheetname As Worksheet Dim indexrowcount As Integer j = 2 iRow = 1 lastline = ActiveSheet.UsedRange.Rows.count While iRow < lastline + 1 a = iRow + 1 b = iRow + 99 ' Max Group Size with Same name in F to H column count = 1 If Cells(iRow, "H").Value = "Vramp_M1" Then sheetname = "Index1" ElseIf Cells(iRow, "H").Value = "Vramp_M2" Then sheetname = "Index2" Else sheetname = "Index3" '<-------error occurs here End If For aRow = a To b If Cells(iRow, "H") = Cells(aRow, "H") And Cells(iRow, "I") = Cells(aRow, "I") And Cells(iRow, "J") = Cells(aRow, "J") Then count = count + 1 Else Set selectRange = Range("A" & iRow & ":AP" & aRow - 1) selectRange.Copy indexrowcount = Sheets(sheetname).UsedRange.Rows.count + 1 Sheets(sheetname).Range("A" & indexrowcount).PasteSpecial xlPasteAll iRow = iRow + count Exit For End If Next aRow Wend