Copy source sheet to end of targeted workbook

A

Annette

I am getting myself confused on how to do this, so I am asking instead. How
do I programmically copy the active spreadsheet (sheet1) to the open
workbook ... into the last position on the workbook?
 
B

Bob Phillips

ActiveSheet.copy
after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
..

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Annette

Well .. that worked supremely ... but I asked the question wrong!

I want to move it to another workbook that is open behind the active one ...
how would I do that. My failure to ask the correct question ... I want to
copy the spreadsheet in the active workbook to the last position in the
second workbook which is open behind the first. (I hope that makes sense).
 
B

Bob Phillips

Assuming that you know the name of the workbook

With Workbooks("SIP 2004.xls")
ActiveWorkbook.ActiveSheet.copy _
after:=.Worksheets(.Worksheets.Count)
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Annette

Now that's a good question .... I think you can see why I'm confused ... I
had used a code similar to this and was not getting anywhere ... I will not
know the name of the workbook. The first one will always be a new workbook
and the second one I'm copying to will change names every month based on the
name of the month (i.e., May.xls, June.xls).

Is it possible to write such a code that could accomplish this feat ... or
is that out of line for Excel?
 
B

Bob Phillips

Dim oWB As Workbook
For Each oWB In Workbooks
If oWB.Name <> Thsisworkbook.Name Then
With oWB
ActiveWorkbook.ActiveSheet.copy _
after:=.Worksheets(.Worksheets.Count)
Exit For
End With
End If
Next oWB

But you have a problem if there are more than 2 workbooks, as there is no
way of knowing which other one will be hit first.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Annette

Cool .. this works .. and I even figured out the little error ... (very
proud of myself!) look below and see if you see it! Thanks, Bob!
 
B

Bob Phillips

Well I got all of the right letters, just too many of them..

I never said I tested it<vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top