Export each worksheet to an individual XLS file

The previous blog entry relates to the same project, but this refers to the creation of the files as XLS files as opposed to the PDF files.

The example code below is designed to take the activeworkbook, and create an XLS file of each and every worksheet with the filename taken from the tab name – ie the invoice number. If you have a similar requirement, feel free to use the code below, or if you need any assistance, contact us through the form below or leave a comment / message.


Sub ExportToXLS()
‘ XLS Export Macro
‘ Change C:\Exports\ to your folder path where you need the files saved
‘ Save Each Worksheet to a separate XLS file.

Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
‘ make a copy to create a new book with this sheet
‘ otherwise you will always only get the first sheet
wsSheet.Copy
‘ this copy will now become active
ActiveWorkbook.SaveAs Filename:=xlsPath + “\” + wsSheet.Name & “.xls”, CreateBackup:=False
ActiveWorkbook.Close

Next wsSheet

End Sub


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *