A few words of thanks would be greatly appreciated.

How to split data into multiple worksheets?

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.
  • Repeat.

Takes a couple of seconds to do and 3 new sheets created.
  • Then name the tabs as needed. Then save your file as .xls

This is probably quicker than creating a macro.


Thanks to sharpman for this tip on the forum.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.


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 (ccm.net).