Thread: split a list into multiply sheets

Hi,

I'm having a long list (about 10000 cells) on one column that i would like to split it into different sheets that each sheet will contain about 200-300 copied cells, doesn't matter what is the content of the cell.

Thanks

Hi, Try this:- The code will Copy column "A" in activesheet in sections of 300 cells, add a new sheet for each new 300 cells, and paste the copied Data to column "A".

Code:

 Sub SpData()
Dim Last As Long, dn As Long, L As Long
Application.ScreenUpdating = False
Last = Range("A" & Rows.Count).End(xlUp).Row

For dn = 1 To Last Step 300
        Range(Cells(dn, "A"), Cells(dn + 299, "A")).Copy
    With Sheets
        .Add After:=Sheets(.Count)
         ActiveSheet.Range("A1").PasteSpecial
    End With
Next dn
Application.ScreenUpdating = True
End Sub

Regards Mick