Create a workbook from every worksheet in your workbook

V

vicky

i have a workbook name test1 which contain 3 sheets named
"aa","bb","cc" .now i need a macro which loop thru test1 workbook and
should create 3 new workbooks with name "aa" ,"bb", "cc" and should
contain data present in their respective sheets.
 
P

Per Jessen

Hi

This should do it:

Sub CopyShToNewWb()

Dim NewWb As Workbook
For Each sh In ThisWorkbook.Sheets
shName = sh.Name
sh.Copy
Set NewWb = ActiveWorkbook
NewWb.SaveAs Filename:=shName
NewWb.Close
Next
End Sub

Regards,
Per
 
J

Jarek Kujawa

ooop!

sorry, forgot about saving new workbooks

Sub cus()
For Each Sheet In ThisWorkbook.Sheets
Sheet.Copy
With ActiveWorkbook
.SaveAs (Sheet.Name)
.Close
End With
Next
End Sub
 
V

vicky

thanku a lot ppl.
but i have a another issue . i want to save the newly created
workbooks in thisworkbook path .
 
G

Gord Dibben

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs FileName:=ActiveWorkbook.Path _
& "\" & w.Name & ".xlsx"
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 

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

Copy Multiple Areas from One Workbook to Another 4
Create Workbooks and then create Worksheets within them 2
EXTRACTING ROWS OF DATA/FORMULAS TO CREATE NEW WORKBOOK 1
Select Multiple Ranges with Certain Data to Print 2
Copy cell value from one worksheet to matching worksheet in another workbook 4
Creating a workbook(s) based upon a cells value <--HELP please 3
loop through the workbook and paste every sheet it into newly createdworkbook with same sheet name 2
Create Combo Box and Populate 3

Top