CODEWITHSUNDEEP
vb.netexcel
MrPatterns
MrPatterns

Reputation: 4434

How do I export the data on an Excel tab to its own Excel file?

I have 10 tabs in 1 Excel file (tabs A-J). I'd like to export the contents of Tab A to its own Excel file named A.xls. Then do the same for B-J. How do I do this?

Is there an option within Excel or must I resort to VB programming?

Upvotes: 0

Views: 284

Answers (2)

dosdel
dosdel

Reputation: 1138

In Excel you should be able to right-click the tab, then go "Move or Copy", and select "New Book". This is still a pretty quick way of going about it, however you could also resort to VB and try something like this:

Sub sheetExport()

Worksheets.Add After:=Worksheets(Worksheets.Count)
For i = 1 To ThisWorkbook.Sheets.Count - 1
    ThisWorkbook.Sheets(1).Move
    ActiveWorkbook.SaveAs ActiveWorkbook.Sheets(1).Name, FileFormat:=51
    ActiveWorkbook.Close SaveChanges:=False  
Next i

End Sub

Upvotes: 0

James Scott
James Scott

Reputation: 1084

I don't know any way to do this from Excel without VBA, as far as VBA goes here's my 2 cents:

Sub exportSheetsToXLSX()

Dim parentWb As Workbook
Dim childWb As Workbook

Set parentWb = ThisWorkbook

For Each ws In parentWb.Worksheets
    Set childWb = Workbooks.Add

    ThisWorkbook.Sheets(ws.Name).Copy before:=childWb.Sheets(1)
    childWb.SaveAs ws.Name, FileFormat:=51
    childWb.Close

Next ws
End Sub

Upvotes: 1

Related Questions