If you dont want to mess aound with VB, you van try this.
I just tested it and it works. It takes less than 10 seconds.

Say your sheet names are MySheet1, MySheet2, MySheet3 ...

Get your sheet names in a column.
Add an extra cell on the top and type in the word "Sheets" to that cell.
So now you have :

Sheets
MySheet1
MySheet2
MySheet3
.....

Now you make a Pivot Table.
The data source is the list of sheets.
In the Layout, put Sheets in the Page section and in the data section as a
Count.
Next, next, next, Done.
You should now have a Pivot table showing a total which should be the total
number of lines in your list of sheets.

Now you need to use the Pivot table Toolbar in order to access a magic
option.
Get your Pivot table Toolbar up in your Toolbars if it's not there already.
Right-click any empty space in the toolbar area and then activate the Pivot
table Toolbar.
It should show now.
Click somewhere in the Pivot table.

Then click on the Pivot table Toolbar and go to the Show Pages option.
It will ask you to choose from the available Pages.
As there is only "Sheets" available, choose "Sheets".

Excel will create the new sheets.

Select all the sheets using the Shift key and your mouse.
Select the cells containing the Pivot table in any sheet.
Delete the rows conating the Pivot table.
Unselect all the sheets.
Select just one.
Done.

Andy