How do I extract multiple tabs from an Excel workbook?

R

Ron Finer

Is there a way to extract all of the tabs in a single workbook so as to
create individual workbook files labeled with the name of each tab? For
example, if I have a single XLS file with 15 tabs, is there an easy way to
extract all 15 tabs to 15 separate XLS files, each named for the name on the
original tab...
 
G

Gord Dibben

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
' For Each W In Worksheets(Array("sheet1", "Sheet3", _
' "Sheet5", "sheet7"))
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path _
& "\" & w.Name & Range("A2").Value
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 08:16:12 -0700, Ron Finer <Ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to break out worksheets from a workbook 2
Recover lost excel tabs/spreadsheets from workbook 2
Mass Changing whole sheets individual cell references 2
sheet with multiple tabs 3
Convert to Values 2
Summary worksheet within a workbook 5
assemble worksheets into one workbook 4
Password protect individual tabs in an excel spreadsheet 1

Top