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 <vimalbarl...@gmail.com> 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" <(E-Mail Removed)> skrev i meddelelsen
news:c975b8ed-a750-4ae4-a1c1-(E-Mail Removed)...
>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 <bli...@gazeta.pl> wrote:
> Sub cus()
> For Each Sheet In ThisWorkbook.Sheets
> Sheet.Copy
> Next
> End Sub
>
> On 10 Lis, 08:37, vicky <vimalbarl...@gmail.com> wrote:
>
>
>
> > i have a workbook name test1 which contain 3 sheets named
> > "aa","bb","cc" .now i need a macro which Â*loop thru test1 workbookand
> > 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 <(E-Mail Removed)>
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" <(E-Mail Removed)> wrote in message news:c975b8ed-a750-4ae4-a1c1-(E-Mail Removed)...
>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.