How to create multiple worksheets from a list of cell values without VBA

Creating Worksheets from a List of Names

Creating multiple worksheets from a list of values is very easy and it could be created by multiple methods. In this article, we will explore ways of creating it by using the following method. Though manually creating worksheets will be very time-consuming and using some tricks could make easy the task.

The first step in creating the task is to list all the values that we wanted to name the worksheets.

list the names for the worksheet
list the names for the worksheet

After listing the name insert a pivot table by using the newly created list as data range. After inserting the pivot table put the name of worksheet column in pivot table Filter as illustrated in the below image.

Creating Worksheets from a List of Names
Creating Worksheets from a List of Names

After selecting this option we will put our mouse cursor on the filter and will go to the PivotTable Analyser tab and then to Active Field columns and there click on PivotTable dropdown. Under PivotTable dropdown select Options and there click on Show Report Filter Pages.

Pivot option
Pivot option

After clicking on this option we will get a new dialog box Show Report Filter Pages and click Ok on it.

Show Report Filter Pages
Show Report Filter Pages

After clicking on it new worksheets will get generated as per names mentioned in the list. These new worksheets are illustrated in the below image.

New worksheet creation.
New worksheet creation.

There is just one issue that we can see is every sheet contains the filter option which we had created earlier. To remove this we will select every excel worksheet by pressing CTRL button. After doing it we will select cell A1 & B1.

deleting cell values from multiple worksheets
deleting cell values from multiple worksheets

Leave a Comment

Your email address will not be published.

fb-share-icon