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!

 

[Jon von der Heyden]

Whew, that means I can sign-off and get to bed, I was worried I would have to stay up working out the error.

 

[johnny0313x]

lol im sure it was a real concern haha. but thank you! I am at work now ...wish I was going to bed blah its 5PM here I don't leave until 8:30PM i hate these 10 hour work days...but you just made my life a little easier....Take care!

 

[Chinarabbit]

Awesome work Jon!

thanks alot.. super useful..

I changes the ranges, I changed XLS to XLSX, and changed 10 to 200, and changed the save location to a different location ending with the name I want them to be named as.. and everything worked perfectly~

 

[juliomanco]

Hi to all, maybe you can help me with my code. I want to copy every 12 rows and create a new csv file for every 12 rows of my sheet. I tried the code published here before, but it always copies the first row to every document created, i just want every 12 rows. This is the code, what to i have to modify? Thanks!

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 12
        Range(.Range("A1:b1").Address(0, 0) & "," & .Range(Cells(i, "A"), Cells(i, "b").Resize(12)).Address(0, 0)).Copy
            Workbooks.Add
            Range("A1").PasteSpecial xlPasteFormats
            Range("A1").PasteSpecial xlPasteValues
            With ActiveWorkbook
                .SaveAs Filename:="C:\File" & i & ".csv"
                .Close
            End With
    Next i
End With

 

[jimmykak]

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

Hello there!

Can i use this code to paste the selected range every time in a word document?

In my case is something like this :

If ws.Name = "Voyage Report" Then
Set wdbmRange = wdDoc.Bookmarks("VoyageReport").Range



For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row Step 21
Range(ws.Range("A1:H1").Address(0, 0) & "," & ws.Range(Cells(i, "A"), Cells(i, "H").Resize(21)).Address(0, 0)).Copy

With wdbmRange
.Select
.PasteSpecial Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, _
DisplayAsIcon:=False
End With
Next i



End If
It open up the word but in the bookmarked place its doesnt appear anything..
Can this be done ?
Thanks in advance!

 

[heresteve2]

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.

Been looking everywhere for a solution like this! I have my header row in A1 to D1. I assume I only need to change the 100 to whatever number rows I want? Even without changing it I get the error:
Runtime Error 1004
Microsoft Excel cannot accese the file 'C:\F8F40500'.

When I click debug I get: .SaveAs Filename:="C:\File" & i & ".xls"

I realize this post is so old that excel versions may have something to do with it, but I cannot find anyone else that has gotten even this close. Even Better would be one that would prompt for the number of lines per sheet I want. I have a different number of lines based on which sheet Im using. But if all I have to do is change that number in the code no big deal.

Thanks in Advance!