Excel: split 1 spreadsheet into numerous sheets

[sjdk]

I have an excel spreadsheet with 150,000+ rows, (20 columns) of data. I need to break that sheet down into spreadsheets with about 15,000 rows in each spreadsheet. (the database I am trying to load these spreadsheets into has limitations) What is the easiest way to do this?

Thanks!

 

[Gruuuu]

Do you need to partition on any particular field? If you CAN do so, I would recommend it, that way your queries can target the correct table and make your queries return faster.
(for example, split the data into years, or department, or region)

Also, is this data that you get on a regular basis?
The reason I ask is it would probably be easier to do this manually if you only have to do it once. If it keeps coming to you in this format, then you will of course need to set some things up to make life easier.

 

[sjdk]

Hi Gruuuu!
This is a one-time deal....it is a spreadsheet of courses that employees have taken. There is no real partition that I could use. The dates/courses/institutins are all over the place. I'm thinking that this would be a manual process, but thought I'd check to see if someone had some trick up their sleeve to make it easher!

Thanks!!!

 

[Gruuuu]

You can't partition the data by Employee Department? Or perhaps Institution? Just anything that would keep you from having to query all the tables. Heck, even if you avoid querying ONE extra table, it will add up.

 

[link99sbc]

Sub Split()

Dim NumbLines: NumbLines = 100 'or whatever
Sheets("Sheet1").Select
Sheets.Add
Sheets("Sheet1").Select
Rows("1:" & NumbLines).Select
Selection.Cut
Sheets("Sheet" & Sheets.Count).Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Selection.Delete Shift:=xlUp
End Sub