Copying aenother workbook's sheets
 

In VBA in Excel 2003, I'm trying, from within one workbook, to open
another workbook, copy one sheet, and past it into sheet 3 of the
calling workbook. I believe I did it something like this:


Dim wbTarget AS Workbook


Set wbWorkbook = "somepath\somesheet.xls".open
wbWorkbook.sheet3.activate
wbWorkbook.sheet3.select
wbWorkbook.sheet3.copy
wbWorkbook.close
ThisWorkbook.sheets(3).activate
ThisWorkbook.sheets(3).cells(1,1).select
ThisWorkbook.sheets(3).cells(1,1).paste


Now, this is from memory of what I left off doing Friday afternoon, so I
may be a bit off. I believe that I had set s string variable to hold
the contents of the COPY operation, but when it was pasted into sheet3
of the calling workbook, all I ended up with was the variable name
itself pasted into the last cell of the copy area - and nothing else.
In other words, all I saw was something like the value strSource in cell
(10, 50) with all other cells on sheet3 blank. Since this is neither
very useful nor what I'm trying to do, I could use a push in the right
direction. My thanks in advance.



Henry
DPM Mellon

Copying aenother workbook's sheets
 

set wb = Workbooks.Open( "somepath\somesheet.xls".)
wb.Worksheets(3).Cells.copy Destination:= _
ThisWorkbook.Worksheets(3).Cells
wb.close SaveChanges:=False

--
Regards,
Tom Ogilvy

"Henry" wrote in message
...
In VBA in Excel 2003, I'm trying, from within one workbook, to open
another workbook, copy one sheet, and past it into sheet 3 of the
calling workbook. I believe I did it something like this:


Dim wbTarget AS Workbook


Set wbWorkbook = "somepath\somesheet.xls".open
wbWorkbook.sheet3.activate
wbWorkbook.sheet3.select
wbWorkbook.sheet3.copy
wbWorkbook.close
ThisWorkbook.sheets(3).activate
ThisWorkbook.sheets(3).cells(1,1).select
ThisWorkbook.sheets(3).cells(1,1).paste


Now, this is from memory of what I left off doing Friday afternoon, so I
may be a bit off. I believe that I had set s string variable to hold
the contents of the COPY operation, but when it was pasted into sheet3
of the calling workbook, all I ended up with was the variable name
itself pasted into the last cell of the copy area - and nothing else.
In other words, all I saw was something like the value strSource in cell
(10, 50) with all other cells on sheet3 blank. Since this is neither
very useful nor what I'm trying to do, I could use a push in the right
direction. My thanks in advance.



Henry
DPM Mellon

Copying aenother workbook's sheets
 

Thanks, Tom. I'll have to give this a shot tomorrow.


Henry


Tom Ogilvy wrote:
set wb = Workbooks.Open( "somepath\somesheet.xls".)
wb.Worksheets(3).Cells.copy Destination:= _
ThisWorkbook.Worksheets(3).Cells
wb.close SaveChanges:=False

Copying aenother workbook's sheets
 

Actually, I see I picked up a stray period when I copied your path

Dim wb as Workbook
set wb = Workbooks.Open( "C:\somepath\somesheet.xls")
wb.Worksheets(3).Cells.copy Destination:= _
ThisWorkbook.Worksheets(3).Cells
wb.close SaveChanges:=False

--
Regards,
Tom Ogilvy


"Henry" wrote in message
...
Thanks, Tom. I'll have to give this a shot tomorrow.


Henry


Tom Ogilvy wrote:
set wb = Workbooks.Open( "somepath\somesheet.xls".)
wb.Worksheets(3).Cells.copy Destination:= _
ThisWorkbook.Worksheets(3).Cells
wb.close SaveChanges:=False

Copying aenother workbook's sheets
 

Thanks for the follow-up. I tried your updated code as follows:

= = = = = = = = = = = = = = = = = = = = = = = = =

Dim strFileName As String
Dim wbSource As Workbook

strFileName = "k:\topside\vba\PF_ACCT_LIST.xls"

Application.ScreenUpdating = False

Set wbSource = Workbooks.Open(strFileName)
wbSource.Worksheets(3).Cells.Copy
Destination:=ThisWorkbook.Worksheets(3).Cells
wbSource.Close

= = = = = = = = = = = = = = = = = = = = = = = = =

but get a "subscript out of bounds" error. Any ideas?


Henry


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

Copying aenother workbook's sheets
 

Incidentally, I did realize that I spelled "another" wrong in the
subject line of the original posting. You know how it is...


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

Copying aenother workbook's sheets
 

Ok, now you can ignore my second-to-last posting. I found the
problem... and yes, it was of the "Doh!" variety. I need to copy from
sheet 1 of the workbook being opened, not sheet 3. This works. I
tested this by deleting everything from sheet 3 of the main (target)
workbook and stepping through the function, and it works. (Or so it
seems at the moment.) I'll let you know, if I may, if anything else
crops up. Thanks again!


Henry
DPM MEllon




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