Macro for Saving tabs in Excel

**peter11111** · 09-14-2009, 07:55 AM

I have a handful of Excel tabs in a large workbook, which I want to save as a web page using a macro.

I am trying to do something along the lines of:

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Documents and Settings\Administrator\Desktop\M3.htm", "M3", "", _
xlHtmlStatic, "Summary Display_381", "")
.Publish (True)
.AutoRepublish = False
End With

but how can do the above for half a dozen tabs?

**jasocke2** · 09-14-2009, 08:37 AM

The following will save each tab in the workbook as a HTML file:

Sub SaveTabsTohtml()
Dim ws As Worksheet

ChDir "C:\test\" ' change as required to where you want to save the files

    For Each ws In Worksheets
        ws.Activate
        ActiveWorkbook.SaveAs Filename:=ws.Name & ".html", _
            FileFormat:=xlHtml, CreateBackup:=False
    Next ws

End Sub

Is this is what you are after?

**karan** · 09-14-2009, 08:25 AM

I have a handful of Excel tabs in a large workbook

Assuming tabs mean worksheets, the solution could be something like:

Application.ScreenUpdating = False
Dim i As Integer

For i = 1 To Sheets.Count
Sheets(i).Activate

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Users\Karan\Desktop\" & Sheets(i).Name & ".htm", Sheets(i).Name, "", _
xlHtmlStatic, "Summary Display_381", "")
.Publish (True)
.AutoRepublish = False
End With
Next

Application.ScreenUpdating = True

--Karan--