Save a single file webpage of each visible sheet in a workbook

[Matt]

I am trying to write a macro that will save a copy of all visible sheets in
my workbook as single file webpages - .mht (one .mht file per sheet).

I have written the following which automatically publishes a single file
webpage for the current sheet but I have no idea how to generalise it to work
on all visible sheets. Can anyone Help?

Sub SaveSFWP()

Application.ScreenUpdating = False
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Documents and Settings\MatthewA\Desktop\" & Range("a1").Text & "
Departure.mht", "Sheet1", "", _
xlHtmlStatic, "Book1_30587", "")
.Publish (True)
.AutoRepublish = False
End With
ChDir "C:\Documents and Settings\MatthewA\Desktop"
End Sub

 

[Ron de Bruin]

Hi Matt

You can adapt this example
http://www.rondebruin.nl/copy6.htm

 

[Matt]

Thanks Ron,

I have used your example and changed my marco to the following:

Sub SaveMHTv2()

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = True Then
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Documents and Settings\MatthewA\Desktop\" & Range("a1").Text
& ActiveSheet.Name & ".mht", ActiveSheet.Name, "", _
xlHtmlStatic, "Book1_30587", "")
.Publish (True)
.AutoRepublish = False
End With
End If
Next sh

ChDir "C:\Documents and Settings\MatthewA\Desktop"

Application.ScreenUpdating = True

End Sub

Unfortunately it is still only saving the first sheet of the workbook as a
single file webpage. I think it is due to the With statement that I am using.

Any idea why it is not running on all the visible sheets?

Thanks

Matt

 

[Ron de Bruin]

Hi Matt

Add a sh.select line to the code
It only use the activesheet now

If sh.Visible = True Then
sh.select

 

[Matt]

Cheers Ron, That has done the trick!

Hi Matt

Add a sh.select line to the code
It only use the activesheet now

If sh.Visible = True Then
sh.select