Splitting up an excel file by rows.

J

johnny0313x

New Member
Joined
Nov 24, 2008
Messages
7
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hi Johnny

Welcome to the board :)

This perhaps;
Rich (BB code): 
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
No problem, so other than a range change does it seem to do the trick?

Code: 
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Jon von der Heyden said:
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.
Click to expand...

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
 
Upvote 0
Jon von der Heyden said:
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.
Click to expand...

IT WORKED!!!! wow...i cant thank you enough! thanks alot man!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA to save as 'Excel Workbook' (not Macro enabled)
Replies
4
Views
288
frabulator
frabulator
N
  • Question
VBA Macro Issue - VBA to automate creating new PP/Excel sheets and updating the links
Replies
0
Views
493
noobslayer252
N
J
  • Solved
Excel vba: Browse to text file and split at each ~ on new row
Replies
3
Views
298
JEC
JEC
J
  • Question
Macro to pick up the last autorecovered file and rename it with the original file name
Replies
1
Views
335
Fluff
Fluff
P
  • Solved
Split one row with a value into multiple lines to add up to this value
Replies
3
Views
333
I_know_nuffin
I

Forum statistics

Threads
1,215,345
Messages
6,124,408
Members
449,157
Latest member
mytux

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top