need a little tweaking to a macro please

Technium

Board Regular
Joined
Jul 9, 2002
Messages
97
Hi

I have a workbook which has lots of worksheets, I have to save each worksheet seperately and to do this I need to delete all the other worksheets in the workbook and select file save as. I managed to find a macro on the internet which I can run which saves all worksheets as individual files but they are still xls files and really I need them saved as htm files. Is there anyone that can look at this code and change abit so it saves as its doing but not as xls files but as htm files please. The code is as follows:-

Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False 'Don't show any screen movement

strSavePath = "C:\Temp\" 'Change this to suit your needs

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next

Application.ScreenUpdating = True

Exit Sub

ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Change:

wbDest.SaveAs strSavePath & sht.Name

To:

wbDest.SaveAs strSavePath & sht.Name, xlHtml
 
Upvote 0
Not trying to be a pain -- but have you tried recording what you want to do ?

You can save a sheet as a webpage by activating the sheet --> File Save As --> WebPage --> then istead of saving Entire Workbook you can select "Republish Sheet."

If this does what you want you can loop your sheets accordingly without needing to save a copy of the file and deleting sheets etc..

Hope this points you in the right direction.
 
Upvote 0
thanks John thats worked just how I wanted it.

Lasw10 I was doing a similar thing each time but when you have about 20 odd worksheets in each workbook it takes awhile whereas the code I had and with a little tweaking from John it takes seconds.

thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top