Help!!

My code below successfully opens a book from the right place, but now Im
trying to copy the sheet Results_first into the book which houses the
below macro, say book X.
How do I tell VB to paste the entire sheet into the book X..?

Regards

D


Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook, bk1 As Workbook
Dim sh1 As Worksheet
Set bk1 = ThisWorkbook
i = 1

sName = Dir("\\server\_to_combine_files\*.xls")


Do While sName <> ""

Set bk = Workbooks.Open("\\server\_to_combine_files\" & sName)


Set sh = bk.Worksheets("Results_first")


*** Sent via Developersdex http://www.developersdex.com ***

sh.Copy Before:=Workbooks("X.xls").Sheets(1)

Change the Workbook and Sheet name to suit your needs.



--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Darin Kramer" wrote:

> Help!!
>
> My code below successfully opens a book from the right place, but now Im
> trying to copy the sheet Results_first into the book which houses the
> below macro, say book X.
> How do I tell VB to paste the entire sheet into the book X..?
>
> Regards
>
> D
>
>
> Dim i As Long, sName As String, sh As Worksheet
> Dim dest As Range, bk As Workbook, bk1 As Workbook
> Dim sh1 As Worksheet
> Set bk1 = ThisWorkbook
> i = 1
>
> sName = Dir("\\server\_to_combine_files\*.xls")
>
>
> Do While sName <> ""
>
> Set bk = Workbooks.Open("\\server\_to_combine_files\" & sName)
>
>
> Set sh = bk.Worksheets("Results_first")
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Try:

sh.Copy Before:=ThisWorkbook.Sheets(1)


--
Hope that helps.

Vergel Adriano


"Darin Kramer" wrote:

> Help!!
>
> My code below successfully opens a book from the right place, but now Im
> trying to copy the sheet Results_first into the book which houses the
> below macro, say book X.
> How do I tell VB to paste the entire sheet into the book X..?
>
> Regards
>
> D
>
>
> Dim i As Long, sName As String, sh As Worksheet
> Dim dest As Range, bk As Workbook, bk1 As Workbook
> Dim sh1 As Worksheet
> Set bk1 = ThisWorkbook
> i = 1
>
> sName = Dir("\\server\_to_combine_files\*.xls")
>
>
> Do While sName <> ""
>
> Set bk = Workbooks.Open("\\server\_to_combine_files\" & sName)
>
>
> Set sh = bk.Worksheets("Results_first")
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

or, if you want the copied sheet to be the last sheet in the workbook, like
this:

sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

> Try:
>
> sh.Copy Before:=ThisWorkbook.Sheets(1)
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Darin Kramer" wrote:
>
> > Help!!
> >
> > My code below successfully opens a book from the right place, but now Im
> > trying to copy the sheet Results_first into the book which houses the
> > below macro, say book X.
> > How do I tell VB to paste the entire sheet into the book X..?
> >
> > Regards
> >
> > D
> >
> >
> > Dim i As Long, sName As String, sh As Worksheet
> > Dim dest As Range, bk As Workbook, bk1 As Workbook
> > Dim sh1 As Worksheet
> > Set bk1 = ThisWorkbook
> > i = 1
> >
> > sName = Dir("\\server\_to_combine_files\*.xls")
> >
> >
> > Do While sName <> ""
> >
> > Set bk = Workbooks.Open("\\server\_to_combine_files\" & sName)
> >
> >
> > Set sh = bk.Worksheets("Results_first")
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> >

Okay, that works... and how would I select all cells in the sheet,
instead of the whole work book..?
ie I now need to go back to source, select all cells, and then goto
destination and paste all cells....

Kind Regards
D



*** Sent via Developersdex http://www.developersdex.com ***

to copy all cells from one worksheet and paste in another, try something like
this:

sh.Cells.Copy Destination:=ThisWorkbook.Sheets("Sheet1").Cells(1, 1)


--
Hope that helps.

Vergel Adriano


"Darin Kramer" wrote:

> Okay, that works... and how would I select all cells in the sheet,
> instead of the whole work book..?
> ie I now need to go back to source, select all cells, and then goto
> destination and paste all cells....
>
> Kind Regards
> D
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>