Hi,

Private Sub CommandButton1_Click()
Dim Wbmain As Workbook
Workbooks.Open ("C:\Source.xls")
Workbooks("Source.xls").Activate
Set Wbmain = ActiveWorkbook
Call CopySheets(Wbmain)
End Sub

Sub CopySheets(Wbmain As Workbook)

Dim wS As Worksheet
Dim wbNew As Workbook
Set wbNew = Workbooks.Add
For Each wS In Wbmain.Worksheets
wS.Copy after:=wbNew.Sheets(wbNew.Sheets.Count)
Next wS
End Sub

Above code creates a new workbook and copy the sheets of the source.xls file
in the new workbook..
I need to paste all the contents of source.xls as single sheet in new
workbook..
(i.e., if source.xls contains 3 sheets, then all the contents of 3 sheets
must be pasted in a single sheet in new workbook)..
Can you please help me with some code..

thank you ,
srikanth

Ron has several examples, this one is pretty close to what you want:
http://www.rondebruin.nl/copy3tip.htm

Mike F
"srikanth" <(E-Mail Removed)> wrote in message
news:FA0BBBF6-63F6-4253-BB84-(E-Mail Removed)...
> Hi,
>
> Private Sub CommandButton1_Click()
> Dim Wbmain As Workbook
> Workbooks.Open ("C:\Source.xls")
> Workbooks("Source.xls").Activate
> Set Wbmain = ActiveWorkbook
> Call CopySheets(Wbmain)
> End Sub
>
> Sub CopySheets(Wbmain As Workbook)
>
> Dim wS As Worksheet
> Dim wbNew As Workbook
> Set wbNew = Workbooks.Add
> For Each wS In Wbmain.Worksheets
> wS.Copy after:=wbNew.Sheets(wbNew.Sheets.Count)
> Next wS
> End Sub
>
> Above code creates a new workbook and copy the sheets of the source.xls
> file
> in the new workbook..
> I need to paste all the contents of source.xls as single sheet in new
> workbook..
> (i.e., if source.xls contains 3 sheets, then all the contents of 3 sheets
> must be pasted in a single sheet in new workbook)..
> Can you please help me with some code..
>
> thank you ,
> srikanth

Thanks a lot Mike.. I got the code and made some arrangements and it works
fine..
thank you a lot...


"Mike Fogleman" wrote:

> Ron has several examples, this one is pretty close to what you want:
> http://www.rondebruin.nl/copy3tip.htm
>
> Mike F
> "srikanth" <(E-Mail Removed)> wrote in message
> news:FA0BBBF6-63F6-4253-BB84-(E-Mail Removed)...
> > Hi,
> >
> > Private Sub CommandButton1_Click()
> > Dim Wbmain As Workbook
> > Workbooks.Open ("C:\Source.xls")
> > Workbooks("Source.xls").Activate
> > Set Wbmain = ActiveWorkbook
> > Call CopySheets(Wbmain)
> > End Sub
> >
> > Sub CopySheets(Wbmain As Workbook)
> >
> > Dim wS As Worksheet
> > Dim wbNew As Workbook
> > Set wbNew = Workbooks.Add
> > For Each wS In Wbmain.Worksheets
> > wS.Copy after:=wbNew.Sheets(wbNew.Sheets.Count)
> > Next wS
> > End Sub
> >
> > Above code creates a new workbook and copy the sheets of the source.xls
> > file
> > in the new workbook..
> > I need to paste all the contents of source.xls as single sheet in new
> > workbook..
> > (i.e., if source.xls contains 3 sheets, then all the contents of 3 sheets
> > must be pasted in a single sheet in new workbook)..
> > Can you please help me with some code..
> >
> > thank you ,
> > srikanth
>
>
>