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.

Sub cus()
For Each Sheet In ThisWorkbook.Sheets
Sheet.Copy
Next
End Sub

On 10 Lis, 08:37, vicky wrote:
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.

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

"vicky" skrev i meddelelsen
...
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.

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

On 10 Lis, 09:05, Jarek Kujawa wrote:
Sub cus()
For Each Sheet In ThisWorkbook.Sheets
Sheet.Copy
Next
End Sub

On 10 Lis, 08:37, vicky wrote:



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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

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

hey got it . chdir thisworkbook.path :)

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

On Tue, 10 Nov 2009 01:14:06 -0800 (PST), vicky
wrote:

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

See also
http://www.rondebruin.nl/copy6.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vicky" wrote in message ...
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.