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

M

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
 
M

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
 
R

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
 
M

Matt

Cheers Ron, That has done the trick!

Ron de Bruin said:
Hi Matt

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

If sh.Visible = True Then
sh.select
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top