Copy source sheet to end of targeted workbook
 

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?

Copy source sheet to end of targeted workbook
 

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

--

HTH

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

"Annette" wrote in message
...
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?

Copy source sheet to end of targeted workbook
 

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


"Bob Phillips" wrote in message
...

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

--

HTH

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

"Annette" wrote in message
...
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?

Copy source sheet to end of targeted workbook
 

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)

"Annette" wrote in message
...
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).


"Bob Phillips" wrote in message
...

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

--

HTH

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

"Annette" wrote in message
...
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?

Copy source sheet to end of targeted workbook
 

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?


"Bob Phillips" wrote in message
...
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)

"Annette" wrote in message
...
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).


"Bob Phillips" wrote in message
...

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

--

HTH

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

"Annette" wrote in message
...
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?

Copy source sheet to end of targeted workbook
 

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)

"Annette" wrote in message
...
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?


"Bob Phillips" wrote in message
...
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)

"Annette" wrote in message
...
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).


"Bob Phillips" wrote in message
...

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

--

HTH

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

"Annette" wrote in message
...
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?

Copy source sheet to end of targeted workbook
 

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!


"Bob Phillips" wrote in message
...
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)

"Annette" wrote in message
...
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?


"Bob Phillips" wrote in message
...
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)

"Annette" wrote in message
...
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).


"Bob Phillips" wrote in message
...

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

--

HTH

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

"Annette" wrote in message
...
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?

Copy source sheet to end of targeted workbook
 

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)

"Annette" wrote in message
...
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!


"Bob Phillips" wrote in message
...
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)

"Annette" wrote in message
...
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?


"Bob Phillips" wrote in message
...
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)

"Annette" wrote in message
...
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).


"Bob Phillips" wrote in
message
...

ActiveSheet.copy

after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Wo rksheets.Count)
.

--

HTH

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

"Annette" wrote in message
...
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?