0

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.

lucky123
  • 39
  • 3
  • 14
  • 2
    Use `ws.Copy` for starters. – BigBen Dec 12 '19 at 14:42
  • 1
    Does this answer your question? [How to copy sheets to another workbook using vba?](https://stackoverflow.com/questions/6863940/how-to-copy-sheets-to-another-workbook-using-vba) – Warcupine Dec 12 '19 at 14:44
  • Thank you both, both of your suggestions are helpful! @BigBen using ws.Copy does what I need it do in terms of creating a new workbook for each tab which is great! The one step I forgot to add in my original post is I need it to also automatically save them into the file destination listed above. – lucky123 Dec 12 '19 at 14:50
  • @BigBen the code above where I'm attempting to export each one as an Excel workbook and saving to the specified file location. – lucky123 Dec 12 '19 at 14:54
  • 1
    Don't export, `SaveAs`. – BigBen Dec 12 '19 at 14:55
  • @BigBen I've updated my code above; it's throwing a runtime error 1004. Does something need to be corrected with my SaveAs? – lucky123 Dec 12 '19 at 15:08
  • @BigBen nm is the worksheet name. Is that what you are asking? – lucky123 Dec 12 '19 at 15:18
  • What is the value of `nm` when the error occurs? – BigBen Dec 12 '19 at 15:19

1 Answers1

2

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • thank you for this! It successfully creates a new workbook for each tab. However, the only issue I'm having now is it includes all of the tabs in each file it creates instead of just the one indicated, if that makes sense. – lucky123 Dec 12 '19 at 15:32
  • Uggh. I was afraid that might happen. I've had issues with `Worksheet.SaveAs` before. See the edit. – BigBen Dec 12 '19 at 15:33