Easy Macro? Split Existing Document into seperate files, no more than 50 rows each, keep 1st column info

[jeffcoleky]

Theoretically this should be easy right?

I have a file with 300 names. It looks something like this (Except MUCH longer):

Excel 2010
	A	B
1	Name	E-Mail
2	Bob	Bob@bogusemail.com
3	Ryan	Ryan@bogusemail.com
4	scott	scott@bogusemail.com
5	Mark	Mark@bogusemail.com
6	Santo	Santo@bogusemail.com
7	Marsha	Marsha@bogusemail.com
8	Sharon	Sharon@bogusemail.com
9	Daniel	Daniel@bogusemail.com
10	Jerry	Jerry@bogusemail.com
Sheet1

I need to create a macro that takes the open document and splits the list into multiple files:

• Keeping the maximum lines to 50 on each file (51 including A1:B1).
• The new name of the files should be the same as the open document, with a "1" or "2" at the end to show it's split.
• Files should be saved to the same folder as the one that's being used (or pre-set to a path, whichever is easier).
• Cells A1 & B1 Should remain the same throughout each file. They never change: Cell A1 is always "Name" and Cell B1 is always "E-Mail"

An example would be:

List.csv (275 Rows total)

--TURNS INTO--

List1.csv -- 51 Rows of data (including the header)
List2.csv -- 51 Rows of data (including the header)
List3.csv -- 51 Rows of data (including the header)
List4.csv -- 51 Rows of data (including the header)
List5.csv -- 51 Rows of data (including the header)
List6.csv -- The last 26 Rows of data from List.csv (including the header)

To an excel guru i'm sure this would be very easy so hopefully my explanation is clear enough that you agree. ANY help is much appreciated!

 

[JLGWhiz]

Yup. You Got it.

PS. I have excel 2010 Professional Plus

Now that is clear and concise. Here is a version modified to create the .csv files. But note that it does not delete the .xlsx files that were created. You should be able to handle that. Also, note that since there was no desired path specified, it will save the .csv files to the same directory as your .xlsx files. You should also be able to handle that.

Code:
Sub newWkBk2()
Dim sh As Worksheet, lr As Long, newWb As Workbook, fName As String, nbr As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
fName = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
nbr = 1
For i = 2 To lr Step 50
Set newWb = Workbooks.Add
newWb.SaveAs fName & nbr & ".xlsx"
sh.Range("A1:B1").Copy newWb.Sheets(1).Range("A1")
sh.Range("A" & i).Resize(50, 2).Copy newWb.Sheets(1).Range("A2")
newWb.SaveAs fName & nbr & ".csv"
nbr = nbr + 1
Next
End Sub
Code:

 

[jeffcoleky]

Oh, that really WAS super easy. I didn't realize that's all you meant so thank you. This will do VERY well so thank you thank you thank you for saving me time with your code.

 

[Momentman]

It was a valiant effort but the result was separate files with only two lines in each.

Just add steps =50 into the code. I forgot that line. That was my only mistake...lol

S

ub split2sheets()
Set wks = ActiveWorkbook
lastrow = Range("A1").End(xlDown).Row
steps = 50
j = 1
For I = 2 To lastrow Step 50
wks.Activate
ActiveSheet.Range(Cells(I, "A"), Cells(I + steps - 1, "B")).Copy
Workbooks.Add
Range("A2").Select
ActiveSheet.Paste
Range(Cells(1, 1), Cells(1, 2)) = Array("NAMES", "E-MAIL")
ActiveWorkbook.SaveAs Filename:="List" & j & ".csv"
ActiveWorkbook.Close
j = j + 1
Next I
End Sub

 

[jeffcoleky]

Just add steps =50 into the code. I forgot that line. That was my only mistake...lol

S

ub split2sheets()
Set wks = ActiveWorkbook
lastrow = Range("A1").End(xlDown).Row
steps = 50
j = 1
For I = 2 To lastrow Step 50
wks.Activate
ActiveSheet.Range(Cells(I, "A"), Cells(I + steps - 1, "B")).Copy
Workbooks.Add
Range("A2").Select
ActiveSheet.Paste
Range(Cells(1, 1), Cells(1, 2)) = Array("NAMES", "E-MAIL")
ActiveWorkbook.SaveAs Filename:="List" & j & ".csv"
ActiveWorkbook.Close
j = j + 1
Next I
End Sub

Thanks for trying again but that only creates one file, and the CSV format doesn't seem to be correct either--it saves it as a xlsx but names it CSV. I don't want to waste more of your time, the other formula from JLGWhiz works great.

 

[Momentman]

Thanks for trying again but that only creates one file, and the CSV format doesn't seem to be correct either--it saves it as a xlsx but names it CSV. I don't want to waste more of your time, the other formula from JLGWhiz works great.

I guess something always goes wrong when i post my code cos it works perfect on mine.

Will try harder next time...lol

 

[ashahlac]

Hi... I'm attempting to replicate this code to separate a datafile into multiple worksheets and I cannot get the code to work. I think I am struggling with file naming and/or reference in the code. Could someone help?