Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook.Activate / Window.Activate problem | Excel Programming | |||
Activate workbook | Excel Programming | |||
Activate Other Workbook | Excel Programming | |||
Activate Workbook | Excel Programming | |||
Activate Workbook | Excel Programming |