Macro (Codes)

[Bonbon]

Hi people, im quite new to macros and theres a few things i need to do
which cant be done using the 'record new macro' function. So does
anyone know a code for:
1)'selecting the second to last sheet of all the worksheets'
2)'selecting the 5th sheet infront of the current sheet'
3)'selecting the 1st sheet infront of the current sheet'

To make it more undastandable heres some examples;

1) total number of 7 sheets, 'A', 'B', 'C'...'F', 'G' - Select sheet
'F'. Im going to have to use this code every week, therfore i cannot
just write a code like 'select sheet F' because everyweek it would be a
different name (because my sheets are named by dates, e.g. M-13.06.05)

2) the current sheet is 'F' - the 5th sheet infront of sheet 'F' would
be sheet 'A'. Similar to above i cant use the code 'select sheet 'A'.

3) the current sheet is 'F' - the 1th sheet infront of sheet 'F' would
be sheet 'E'. Similar to above i cant use the code 'select sheet 'A'.

If you need the actualy names of the sheets because of any reasons they
are (in order of the 7 sheets);
'M-13.06.05', 'Tu-14.06.05', 'W-15.06.05', 'Th-16.06.05', 'F-17.06.05',
'WSR-13.06.05 - 17.06.05', 'WSP-13.06.05 - 17.06.05'.

If there is no such code, please tell me is there any similar ways?

Any help would be hugely appreciated,
Bonbon =)

 

[Guest]

You can select sheets by their Tab order, as long as you don't rearrange the
sheets. For example, to select the sheet to the left of the active sheet do:

Sheets(ActiveSheet.Index - 1).Select

just make sure you aren't already at sheet 1. Or you can select a tab
directly:

Sheets(5).Select

(as long as you have at least 5 sheets)

 

[JE McGimpsey]

One way:

1)

With ActiveWorkbook.Worksheets
If .Count >= 2 Then .Item(.Count - 1).Select
End With

2)

With ActiveSheet
If .Index >= 6 Then Worksheets(.Index - 5).Select
End With

3)

With ActiveSheet
If .Index >= 2 Then Worksheets(.Index - 1).Select
End With

 

[Bob Phillips]

Would it not be easier to have a (more) standard naming convention (ddd
dd.mm.yy) and then pick the sheet based upon the current date
(format(date-7,"ddd dd.mm.yy")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bonbon]

Thanks for all the help, and especially JE McGimpsey because his way
worked.

However afterwards I needed to use the codes to create a formula which
is sums up the first 5 sheets' (infront of the current sheet) cell
'D4'. and have that total in the current sheets cell D4. However it
doesnt work =(

You must think, why dont i just click on each cell whilst creating the
formula, well i couldnt because this macro needs to run everyweek, and
if i selected those cells, it will repeat totalling those specific
cells and not the ones its suppose to.

Pls help

thx in advance
Bonbon