Thread: how to create (or cut into) mutiple spreadsheets from one single/large spreadsheet

Hi, Hope everyone is doing great.

I have an excel spreadsheet that has 100 columns and almost 1000 rows. It's a research data. How can I create a spreadsheet from the above spreadsheet and make say 10 small spreadsheets out of it automatically (i.e. not manually).

say into 10 spreadsheets each with 10 columns and 100 rows.
I am doing it manually and it takes foreever as my PC hangs when i select few chunks.

Thanks for your help.

Regards, rucshaca

Hi rucshaca

you could adjust and use something like

Option Explicit
Sub test()
    Dim Rowcount As Long, Xrow As Long, XSheet As String
    With Sheets("Sheet1")
        Rowcount = .Cells(Rows.Count, 1).End(xlUp).Row
        Xrow = 1
        Do While Xrow < Rowcount
            Sheets.Add After:=Sheets(Sheets.Count)
            XSheet = Sheets(Sheets.Count).Name
            .Range("A" & Xrow, .Range("H" & Xrow + 10)).Copy (Sheets(XSheet).Range("A1"))
            Xrow = Xrow + 10
        Loop
    End With
End Sub