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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
  • Solved
Splitting up an excel file to multiple tabs by "visible" rows.
2
Replies
11
Views
199
BasicUserWithExp
B
amadams
  • Question Question
Macro to AutoSave Excel Spreadsheets Using Different File Names
Replies
1
Views
449
NdNoviceHlp
NdNoviceHlp
B
  • Question Question
VBA to save as 'Excel Workbook' (not Macro enabled)
Replies
4
Views
968
frabulator
frabulator
Y
  • Solved
Issue with HorizontalAlignment
Replies
8
Views
407
Peter_SSs
Peter_SSs
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
550
Amo840812
A

Forum statistics

Threads
1,223,936
Messages
6,175,507
Members
452,650
Latest member
Tinfish

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