I am having a sheet of 20k plus rows(Source Sheet). It is sorted on column "D". Basing on the values in column "D" I would like to create separate sheet for the rows having same values in column "D". i.e., a new sheet is created as and when a change in value is encountered. I want to name the new sheet with relevant value from column "D". Give me a macro please.
The following code trowing run time error when it encountered 1st change in column "D" and stopping after 1st sheet is created.
Dim firstrow, lastrow, r, torow As Integer Dim fromsheet, tosheet As Worksheet firstrow = 2 Set fromsheet = ActiveSheet lastrow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row For r = firstrow To lastrow If fromsheet.Cells(r, "B") <> "" Then 'skip rows where column D is empty
On Error GoTo make_new_sheet Set tosheet = Worksheets("" & fromsheet.Cells(r, "D")) On Error GoTo 0 GoTo copy_row make_new_sheet: Set tosheet = Worksheets.Add(After:=Worksheets(Worksheets.Count)) tosheet.Name = fromsheet.Cells(r, "D") copy_row: torow = tosheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 fromsheet.Cells(r, 1).EntireRow.Copy
tosheet.Cells(torow, 1).PasteSpecial Paste:=xlPasteValues End If Next r Application.CutCopyMode = False fromsheet.Activate