activate last worksheet in a different workbook
 

Hi

I'm trying to write a macro that will need to copy the last worksheet from a
different workbook into the one I'm working on. The last worksheet changes
each month (ie, presently it's August 06, next month it will be September 06
etc.), so I can't refer to it by name, and don't think by index numbers.
Anyone know of a way to do this?

Thanks

activate last worksheet in a different workbook
 

Perhaps the best way would be to cycle through all of the worksheets and
store the name of it in each loop. then when the loop finished you have the
name of the last worksheet in the workbook stored.

For example:

dim wsheet as worksheet
dim LastWorkSheetName as string
For Each wsheet In Worksheets
LastWorkSheetName = wsheet.NAME
Next wsheet

"Aaron" wrote in message
...
Hi

I'm trying to write a macro that will need to copy the last worksheet from
a
different workbook into the one I'm working on. The last worksheet changes
each month (ie, presently it's August 06, next month it will be September
06
etc.), so I can't refer to it by name, and don't think by index numbers.
Anyone know of a way to do this?

Thanks

activate last worksheet in a different workbook
 

bk.sheets(sheets.count).copy After:=Thisworkbook _
.Sheets(thisworkbook.Sheets.count)

--
Regards,
Tom Ogilvy



"Aaron" wrote:

Hi

I'm trying to write a macro that will need to copy the last worksheet from a
different workbook into the one I'm working on. The last worksheet changes
each month (ie, presently it's August 06, next month it will be September 06
etc.), so I can't refer to it by name, and don't think by index numbers.
Anyone know of a way to do this?

Thanks

activate last worksheet in a different workbook
 

Hi

Thanks to you both. VBA code is not something I'm overfamiliar with, as a
result I have little idea what either of these solutions is actually meant to
do, or how to "customise" them so they refer to the specific workbooks I'm
trying to fiddle with. Could you possibly let me know which parts I need to
change to make this work with my workbooks?

Thanks

"Tom Ogilvy" wrote:

bk.sheets(sheets.count).copy After:=Thisworkbook _
.Sheets(thisworkbook.Sheets.count)

--
Regards,
Tom Ogilvy



"Aaron" wrote:

Hi

I'm trying to write a macro that will need to copy the last worksheet from a
different workbook into the one I'm working on. The last worksheet changes
each month (ie, presently it's August 06, next month it will be September 06
etc.), so I can't refer to it by name, and don't think by index numbers.
Anyone know of a way to do this?

Thanks

activate last worksheet in a different workbook
 

dim bk as Workbook
set bk = Workbooks("DifferentBook.xls")
bk.sheets(sheets.count).copy After:=Thisworkbook _
.Sheets(thisworkbook.Sheets.count)

--
Regards,
Tom Ogilvy


"Aaron" wrote:

Hi

Thanks to you both. VBA code is not something I'm overfamiliar with, as a
result I have little idea what either of these solutions is actually meant to
do, or how to "customise" them so they refer to the specific workbooks I'm
trying to fiddle with. Could you possibly let me know which parts I need to
change to make this work with my workbooks?

Thanks

"Tom Ogilvy" wrote:

bk.sheets(sheets.count).copy After:=Thisworkbook _
.Sheets(thisworkbook.Sheets.count)

--
Regards,
Tom Ogilvy



"Aaron" wrote:

Hi

I'm trying to write a macro that will need to copy the last worksheet from a
different workbook into the one I'm working on. The last worksheet changes
each month (ie, presently it's August 06, next month it will be September 06
etc.), so I can't refer to it by name, and don't think by index numbers.
Anyone know of a way to do this?

Thanks

activate last worksheet in a different workbook
 

Thanks Tom, think I see how it works now. Simplified it a bit to this, seems
to do the trick:

Workbooks.Open "s:\Otherworkbook.xls"
Worksheets(Worksheets.Count).Activate

Presume your code does it without needing to open the other workbook first!

"Tom Ogilvy" wrote:

dim bk as Workbook
set bk = Workbooks("DifferentBook.xls")
bk.sheets(sheets.count).copy After:=Thisworkbook _
.Sheets(thisworkbook.Sheets.count)

--
Regards,
Tom Ogilvy


"Aaron" wrote:

Hi

Thanks to you both. VBA code is not something I'm overfamiliar with, as a
result I have little idea what either of these solutions is actually meant to
do, or how to "customise" them so they refer to the specific workbooks I'm
trying to fiddle with. Could you possibly let me know which parts I need to
change to make this work with my workbooks?

Thanks

"Tom Ogilvy" wrote:

bk.sheets(sheets.count).copy After:=Thisworkbook _
.Sheets(thisworkbook.Sheets.count)

--
Regards,
Tom Ogilvy



"Aaron" wrote:

Hi

I'm trying to write a macro that will need to copy the last worksheet from a
different workbook into the one I'm working on. The last worksheet changes
each month (ie, presently it's August 06, next month it will be September 06
etc.), so I can't refer to it by name, and don't think by index numbers.
Anyone know of a way to do this?

Thanks