Splitting up an excel file by rows.

[johnny0313x]

I am trying to split up an excel file that has 18000 lines, i want to break this into groups of 100 lines and retain the original header info on line one for each file. Is this possible to do with a macro. I have NO experience with macros of programming so go easy on me. It would be great if it could autosave as book 1 book 2 book 3 or whatever, file name doesn't matter that much. Just breaking apart manually is so boring and annoying. Thank you!

 

[johnny0313x]

anyone, im desperate? i researched so much and go no place everytime...

 

[Jon von der Heyden]

Hi Johnny

Welcome to the board

This perhaps;

Sub test()
Dim iCalc As Long, i As Long
iCalc = Application.Calculation
With Application
    .Calculation = xlManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
 
With ThisWorkbook.ActiveSheet
    For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Step 100
        Range(.Range("A1:D1").Address(0, 0) & "," & .Range(Cells(i, "A"), Cells(i, "D").Resize(100)).Address(0, 0)).Copy
            Workbooks.Add
            Range("A1").PasteSpecial xlPasteValues
            With ActiveWorkbook
                .SaveAs Filename:="C:\File" & i & ".xls"
                .Close
            End With
    Next i
End With
With Application
    .Calculation = iCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

The sheet with the data needs to be active when the macro is run.

Assumes column labels in A1:D1, and that data is housed in same columns.

Change ranges to suit.

Hope this helps.

 

[johnny0313x]

THanks, I tried it out, it selected 100 items which actually my data goes from A1 - O1 and about 1800 rows. Maybe I pasted it in the wrong spot like I said I dont no much about any of this. If you can give me a step by step procedure, that would be amazing. I no thats asking alot. Thank you again for responding.

 

[Jon von der Heyden]

No problem, so other than a range change does it seem to do the trick?

Sub test()
Dim iCalc As Long, i As Long
iCalc = Application.Calculation
With Application
    .Calculation = xlManual
    .ScreenUpdating = False
    .EnableEvents = False
End With

With ThisWorkbook.ActiveSheet
    For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Step 100
        Range(.Range("A1:[B][COLOR=red]O[/COLOR][/B]1").Address(0, 0) & "," & .Range(Cells(i, "A"), Cells(i, "[B][COLOR=red]O[/COLOR][/B]").Resize(100)).Address(0, 0)).Copy
            Workbooks.Add
            Range("A1").PasteSpecial xlPasteValues
            With ActiveWorkbook
                .SaveAs Filename:="C:\File" & i & ".xls"
                .Close
            End With
    Next i
End With
With Application
    .Calculation = iCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

 

[johnny0313x]

where am i suppose to paste this...what i have been doing is going to tools>macros>Visual Basic editor> Double clicking on Sheet1 in the VBA project browser and pasting it in there then closing it going back to my sheet and running the macro. Is that correct?

 

[Jon von der Heyden]

Sorry, I should have said...


Go to the VBE (Alt + F11) then go to Insert > Module and paste it into the code pane (large white pane)...

Also;

This will do the autosave as you requested. It will save each file to your C drive. You need to open them to check them.

Hope this helps.

 

[johnny0313x]

Sorry, I should have said...


Go to the VBE (Alt + F11) then go to Insert > Module and paste it into the code pane (large white pane)...

Also;

This will do the autosave as you requested. It will save each file to your C drive. You need to open them to check them.

Hope this helps.

Well i tried it o ut and it selected everything right, but then the VB editor thing popped up with an error that said 400

 

[Jon von der Heyden]

Johnny

If you click "Debug" what line does it highlight in yellow?

 

[johnny0313x]

Sorry, I should have said...


Go to the VBE (Alt + F11) then go to Insert > Module and paste it into the code pane (large white pane)...

Also;

This will do the autosave as you requested. It will save each file to your C drive. You need to open them to check them.

Hope this helps.

IT WORKED!!!! wow...i cant thank you enough! thanks alot man!