activate last worksheet in a different workbook

[Aaron]

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

 

[Graham Whitehead]

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

 

[Tom Ogilvy]

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

 

[Aaron]

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]

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

 

[Aaron]

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!