Macro (Codes)

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 =)

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)


"Bonbon" wrote:

>
> 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 =)
>
>
> --
> Bonbon
> ------------------------------------------------------------------------
> Bonbon's Profile: http://www.excelforum.com/member.php...o&userid=31866
> View this thread: http://www.excelforum.com/showthread...hreadid=515940
>
>

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

In article <Bonbon.23p79z_1140724805.7074@excelforum-nospam.com>,
Bonbon <Bonbon.23p79z_1140724805.7074@excelforum-nospam.com> wrote:

> 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 =)

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" <Bonbon.23p79z_1140724805.7074@excelforum-nospam.com> wrote in
message news:Bonbon.23p79z_1140724805.7074@excelforum-nospam.com...
>
> 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 =)
>
>
> --
> Bonbon
> ------------------------------------------------------------------------
> Bonbon's Profile:
http://www.excelforum.com/member.php...o&userid=31866
> View this thread: http://www.excelforum.com/showthread...hreadid=515940
>

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