Switching between spreadsheets

[solomon_monkey]

Final thing today...

I want to switch between worksheets in a macro, however, they will be
called slightly different things each time... each week it will be +1
last week... e.g. 'Week1Worksheet' will next week be
'Week2Worksheet' etc. (The number is the only thing that will be
different the text will be constant)- Is there a way to select the
sheet I want so I can switch between?
Help on this would be SO much appreciated.

Thanks

Solomon

 

[Guest]

Using code that actually selects sheets is best avoided since it considerably
slows down execution.

However,

If you define a variable such as

Dim WeekNum as integer

then

Sheets("Week" & WeekNum & "Worksheet").Select

will have the effect you desire.

A loop of some kind may be useful to you using WeekNum as the index.

Nick Shinkins

 

[Tom Ogilvy]

If lastweek is the activesheet and the new sheet is the next in tab order:

Dim sh as Worksheet, sh1 as Worksheet
set sh = Activesheet
set sh1 = Activesheet.Next
msgbox sh.Name & " " & sh1.Name
Sh1.Range("A1").Value = sh.range("A1").Value