Excel can be used dynamically to handle data and is one of the most popular applications in the Office software range. One such application is to split data into multiple worksheets
. One approach could be to record or write a macro
. However, if this action is to be done on a limited number of worksheets
then it would be more helpful if one uses the traditional cut-paste method
. Here one can easily cut the data, open a new worksheet
and paste the data onto the new sheet.
I hope someone can help! I have a .csv file open with Excel that contains a large amount of data that has been merged together so that each data set follows each other vertically. All the columns are aligned and the column headers are repeated in a row each time a new set starts. What I want to do is use a macro that splits these data sets so that each one is on a separate worksheet that is numbered consecutively...then save it as an .xls file. An example of what I'm dealing with can be downloaded from http://users.aber.ac.uk/hhp06/merge.csv
Is there a simple script that could manage this? Your help would be much appreciated!
A simple way would be the old cut and paste,
- Highlight all the rows for the first set of data,
- Press CTRL+X
- Click on new sheet
- Press CTRL+ V
- Click on the merge tab, delete the rows, as all there are is the same amount of rows in each set, this will highlight the next set.
Takes a couple of seconds to do and 3 new sheets created.
This is probably quicker than creating a macro.
- Then name the tabs as needed. Then save your file as .xls
Thanks to sharpman for this tip on the forum.
This document, titled « How to split data into multiple worksheets? », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM