Selecting Last but one worksheet 1

[funkmonsteruk]

I need to select the last but one worksheet in a workbook - the name of the worksheet changes everyday, but it's position remains constant.

Any Suggestions

 

[RobBroekhuis]

Use

sheets(sheets.count-1).activate

Rob

 

[funkmonsteruk]

Cheers Mate....

 

[funkmonsteruk]

Just one more thing along the same lines - I need to select columns B and insert a name (define as named range).

Using your code i've got

ActiveWorkbook.Sheets(Sheets.Count - 1).Activate
Columns("A:C&quot.Select
ActiveWorkbook.Names.Add Name:=strText1

However my bit of the code doesn't function, any ideas

 

[xlbo]

what does strText1 evaluate to ?? Rgds
~Geoff~

 

[funkmonsteruk]

strText is a string and it equals format(Date, "mmmdd&quot

 

[RobBroekhuis]

You need to modify it as follows:

activeworkbook.Names.Add Name:=strText1, refersto:="='" & activesheet.name & "'!$A:$C"

Rob

 

[funkmonsteruk]

Cheers once again Rob - you really are a life saver and i'm gonna give you a star for this one.....

 

[Loomah]

...and what exactly goes wrong, any error messages?
Though I guess this isn't your problem, using

Sheets(Sheets.Count - 1).Activate

will activate a chart sheet if a chart sheet happens to be the last sheet but 1. In which case there are no columns to select!

To make sure this isn't the problem and it never will be I'd recommend substituting 'Sheets' with 'Worksheets'

Just a thought!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

There's definately something wrong with my connection!!
3 posts in the time it takes to send one - gotta be a record!

Feel free to ignore me!
;-) If a man says something and there are no women there to hear him, is he still wrong?