| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting a text file into individual worksheets
I have a large (8,000 lines) text file that I need to import into Excel. The
thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
Hi Sam
After you open the file in excel you can use the code from this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
Thanks, Ron. Looks like #3 will do what I want.
I'm afraid I have no experience with VBA. Can you point me to step-by-step instructions on how to use this code? Thanks again, Sam. "Ron de Bruin" wrote: Hi Sam After you open the file in excel you can use the code from this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
Each department number appears in its own column?
If yes, try importing the data into one worksheet, then try... Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sam Chambers wrote: I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
Ron:
I've been looking through some of the other examples on your site, and I think your "Create separate sheet for each horizontal PageBreak" code would be easier. I still have no idea how to use it, but it sure looks like it would work! Sam. "Ron de Bruin" wrote: Hi Sam After you open the file in excel you can use the code from this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
Never mind...I figured out where to put the code, and the "Create separate
sheet for each horizontal PageBreak" code worked beautifully! Thanks very much, Ron! You just saved me a ton of time! Sam. "Sam Chambers" wrote: Ron: I've been looking through some of the other examples on your site, and I think your "Create separate sheet for each horizontal PageBreak" code would be easier. I still have no idea how to use it, but it sure looks like it would work! Sam. "Ron de Bruin" wrote: Hi Sam After you open the file in excel you can use the code from this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
Hi Sam
In which column are the department numbers? Which row is your header row and in which column start your data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... Thanks, Ron. Looks like #3 will do what I want. I'm afraid I have no experience with VBA. Can you point me to step-by-step instructions on how to use this code? Thanks again, Sam. "Ron de Bruin" wrote: Hi Sam After you open the file in excel you can use the code from this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting a text file into individual worksheets
OK, that's good
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... Never mind...I figured out where to put the code, and the "Create separate sheet for each horizontal PageBreak" code worked beautifully! Thanks very much, Ron! You just saved me a ton of time! Sam. "Sam Chambers" wrote: Ron: I've been looking through some of the other examples on your site, and I think your "Create separate sheet for each horizontal PageBreak" code would be easier. I still have no idea how to use it, but it sure looks like it would work! Sam. "Ron de Bruin" wrote: Hi Sam After you open the file in excel you can use the code from this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sam Chambers" wrote in message ... I have a large (8,000 lines) text file that I need to import into Excel. The thing is that I need to get the file into separate worksheets, based on department number. The departments change about every 50 lines or so (it's variable). I've racked my brain and can't think of a way to do this, other than doing it manually. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| splitting data across worksheets | Excel Discussion (Misc queries) | |||
| Importing XML files into individual worksheets | Excel Discussion (Misc queries) | |||
| Splitting data in a single cell that is seperated by commas, then moving to make individual rows | Excel Discussion (Misc queries) | |||
| password protect individual worksheets | Excel Discussion (Misc queries) | |||
| Copying many tabs from one file to an individual file for each tab | Excel Discussion (Misc queries) | |||
![Sam Chambers[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
