Hello experts. I need to break up my workbook into separate files for
each sheet. The plan is to save it as HTML (which produces an html
file for each sheet). When saving my excel workbook as html, I would
like to specify the name of each sheet. Right now when you save a
workbook it automatically names each sheet (sheet001.htm,
sheet002.htm, etc.) even when they are explicitly named in the
workbook. How can I specify these names?

Alternatively can I break up the excel workbook into a different
workbook for each sheet? Again, I want to specify the file name.

To save each sheet as a new *.html file using the current sheet names.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & _
"\" & w.Name, FileFormat:= xlHtml
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

If you mean from a list of names on a sheet use this to first change the sheet
names in your original workbook.

Sub NameWS()
're-name 10 sheets with list in A1:A10 on first sheet
'make sure your list range has enough names to cover the number of sheets
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub

The Make_New_Books macro will close the original workbook and not ask to save
changes.


Gord Dibben MS Excel MVP


On 5 May 2007 12:03:10 -0700, wrote:

Hello experts. I need to break up my workbook into separate files for
each sheet. The plan is to save it as HTML (which produces an html
file for each sheet). When saving my excel workbook as html, I would
like to specify the name of each sheet. Right now when you save a
workbook it automatically names each sheet (sheet001.htm,
sheet002.htm, etc.) even when they are explicitly named in the
workbook. How can I specify these names?

Alternatively can I break up the excel workbook into a different
workbook for each sheet? Again, I want to specify the file name.

that kicked ass! thanks :) !

Thanks for the feedback.

Gord

On 6 May 2007 20:23:37 -0700, wrote:

that kicked ass! thanks :) !