define destination sheet

D

Darin Kramer

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")
 
G

Guest

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

Change the Workbook and Sheet name to suit your needs.
 
G

Guest

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)
 
D

Darin Kramer

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
 
G

Guest

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top