CODEWITHSUNDEEP
excelvba
lucky123
lucky123

Reputation: 39

How to export tabs in a workbook to individual excel workbooks with button click

I need to create a button press that will automatically export each tab in the workbook to an individual Excel workbook.

This is the code I'm currently using; what do I need to change?

Sub ExportToXLSX()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Copy
nm = ws.Name
ws.SaveAs ("C:\Users\username\Desktop\Box 2 Files\" & nm & ".xlsx")

Next ws

End Sub

Edit: I also need these individual tabs to save to the specified file destination in addition to exporting into individual workbooks.

Upvotes: 0

Views: 2356

Answers (1)

BigBen
BigBen

Reputation: 50007

Here's an approach using Worksheet.Copy to create a new workbook.

Sub ExportToXLSX()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:="C:\Users\username\Desktop\Box 2 Files\" & ws.Name & ".xlsx", _
                              FileFormat:=xlOpenXMLWorkbook
    Next  
End Sub

Upvotes: 2

Related Questions