i have a workbook which contain many sheets . i have to loop through
the workbook and paste every sheet it into newly created workbook
with same sheet name... hope this is possible .

If you are not adding to an existing workbook, you could just as easily do a
SaveAs on the source workbook to the Filename you want to use for the new
workbook. This would give you the new workbook with the sheets having the
same name and content.



"vicky" wrote in message
...
i have a workbook which contain many sheets . i have to loop through
the workbook and paste every sheet it into newly created workbook
with same sheet name... hope this is possible .

The code below will create a workbook wiht 10 sheets named Joel1 to
Joel10

Sub NameSheets()

SheetName = "Joel"
NumberofSheets = 10


ShtCount = Sheets.Count

For i = 1 To NumberofSheets
If i ShtCount Then
Sheets.Add after:=Sheets(Sheets.Count)
End If

Sheets(i).Name = SheetName & i
Next i


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=152357

Microsoft Office Help

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 which contain many sheets . i have to loop through
the workbook and paste every sheet it into newly created workbook
with same sheet name... hope this is possible .