Prev: Limit due to selected cells
Next: Copy worksheet, trying to paste to new workbook error assistance.
From: Rusty_Excelman on 6 May 2010 05:00
I have automatically created a macro - however would like to modify it so
that it uses data in cell fields rather than hardcoded values in the macro.
For example: sheet selection currerntly is

sheets("wk3").Select

I want to make this more flexible by using a value in cell B2 that contains
the value "wk3". I have attempted unsuccessfully to change the macro as it
errors when I put in the cell reference B2. By doing this it will mean I no
longer need to change the macro each time when the week changes - I just need
to change the value in B2 to the current sheet name.

If the reponse could also include referencing the sheet name for the cell B2
as well that would be great - e.g. Summary!B2 or whatever the answer.

Many thanks

From: Jacob Skaria on 6 May 2010 05:17
Try

Sub Macro()
Dim ws As Worksheet
Set ws = Worksheets(Worksheets("Summary").Range("B2").Text)
ws.Select
End Sub


PS: Check out help on named ranges...

--
Jacob (MVP - Excel)


"Rusty_Excelman" wrote:

> I have automatically created a macro - however would like to modify it so
> that it uses data in cell fields rather than hardcoded values in the macro.
> For example: sheet selection currerntly is
>
> sheets("wk3").Select
>
> I want to make this more flexible by using a value in cell B2 that contains
> the value "wk3". I have attempted unsuccessfully to change the macro as it
> errors when I put in the cell reference B2. By doing this it will mean I no
> longer need to change the macro each time when the week changes - I just need
> to change the value in B2 to the current sheet name.
>
> If the reponse could also include referencing the sheet name for the cell B2
> as well that would be great - e.g. Summary!B2 or whatever the answer.
>
> Many thanks
>
From: Mike H on 6 May 2010 05:20
Hi,

Something like this

sht = Sheets("Sheet1").Range("B3").Value
Sheets(sht).Select

or is it this

sht = ActiveSheet.Range("B3").Value
Sheets(sht).Select
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Rusty_Excelman" wrote:

> I have automatically created a macro - however would like to modify it so
> that it uses data in cell fields rather than hardcoded values in the macro.
> For example: sheet selection currerntly is
>
> sheets("wk3").Select
>
> I want to make this more flexible by using a value in cell B2 that contains
> the value "wk3". I have attempted unsuccessfully to change the macro as it
> errors when I put in the cell reference B2. By doing this it will mean I no
> longer need to change the macro each time when the week changes - I just need
> to change the value in B2 to the current sheet name.
>
> If the reponse could also include referencing the sheet name for the cell B2
> as well that would be great - e.g. Summary!B2 or whatever the answer.
>
> Many thanks
>
 | 
Pages: 1
Prev: Limit due to selected cells
Next: Copy worksheet, trying to paste to new workbook error assistance.