Split worksheets by number of rows...

[fjdurbin]

I had found some brilliant code at: http://www.mrexcel.com/forum/showthread.php?t=396069

The problem with this code is that I end up with hundreds of worksheets.

I'm wondering if it could be modified according to my need or if there is an easier way to accomplish my need. Rather than using a key value to trigger the creation of a new worksheet, I want to trigger a new worksheet based upon the number of rows HOWEVER I don't want to split my key value between multiple worksheets.

For example, I have a csv file with about 200k rows of records with account numbers. 30k rows are account 123456789 and 30k rows are account 987654321 and and 25k rows are account 123455555. Since the rows are sorted I would want the 30k account 123456789 and the 25k 123455555 on sheet1 and the 30k account 987654321 on sheet2. My total number of worksheets will be around 3 or 4.

I hope that makes sense and that you can help me. Thank you...JD

 

[jim may]

I would hope that your code enbraces the use of the Auto-Filter; If it doesn't, it should.
Why not Record yourself a Macro to get started?

 

[Weaver]

Are you on 2007 or above? I.e are you able to load 200k+ rows in one go?

 

[fjdurbin]

Are you on 2007 or above? I.e are you able to load 200k+ rows in one go?

I'm using Excel 2007 but most of the users across the company that will access the files from SharePoint are Excel 2003 users.

In all there are 14 csv files that I'm processing with one macro. It opens each file, loads the rows then processes the data and then saves the end result in Excel 2003 then it moves to the next file. Most of the 14 files are always going to be under 65k rows. Only one will always be in the 200k range. Thanks...JD

 

[fjdurbin]

I would hope that your code enbraces the use of the Auto-Filter; If it doesn't, it should.
Why not Record yourself a Macro to get started?

I'm fairly proficient with Excel and VBA. I don't write much original code but I can usually find code and modify it to my needs. The code here: http://www.mrexcel.com/forum/showthread.php?t=396069 does most of what I need it to do but I haven't figured out a way to change it so that the new worksheets are only added after 65k rows. Thanks...JD

 

[Weaver]

If any of the csv sheets are > 65k rows and you're trying to import them into 2003 then you're going to have to do it one line at a time, direcly from the csv. This might fit in a little with something I'm trying to do for my own needs.

I'm thinking:
1. get the filename of csv
2. use line input to get the next record
3. check to see if the record has a sheet to go to
4. if not create one
5. if it does and it's full, create a new one (naming conventions could be an issue)
6. save the data in the appropriate place
7. back to 2 until the file is complete
8. back to 1 until there are no more files

 

[fjdurbin]

I am doing something very similar. My routine is:

1. I run a macro in Excel 2007 that asks me to select the folder that contains the files that will be processed
2. The macro creates a list of the files
3. The macro opens each file in the list and removes blank lines, sub totals, and extraneous columns. It also adds autofilter, freeze panes, and resizes columns and rows.
4. The macro then closes the file and saves it as xls with a name that includes the parameters used during the creation of the csv. Then the macro goes on and does the same thing to the next file contained in the list.

Right now I have to include a step to count the rows in each file and if the number is >65k then the macro saves the file as xlsx instead of xls. My hope is to divide a worksheet that contains more than 65k rows into multiple worksheets using a combination of account number and the number or rows. If I used account number only as the key I would end up with 300 or more worksheets.

I've created a routine to list and count unique values and now I'm attempting to add to the routine a way to do a running sum on the count of unique values and every time the running sum NEARS 65k to create a new worksheet in such a way that a GROUP of accounts is not divided between two worksheets.

I'm getting closer but my logic is not properly calculating the start and end dates of the third (and fourth, fifth, etc. if there are any) worksheet(s).