How to split data into multiple worksheets?

October 2016

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

Related :

This document entitled « How to split data into multiple worksheets? » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.