I've got a workbook with several different sheets. I want a macro
that will select the required sheet depending on what cell is
highlighted.

For example there are cells for Jan, through to Dec. If the Mar cell
is highlighted, when the user clicks on the macro button I want it to
open the sheet for Mar and do some processing. I know how to do the
processing but how do you change what sheet is active based on this
kind of user action?

Thanks!

Hi David -

Here's a basic starting point:

Private Sub CommandButton1_Click()
Worksheets(ActiveCell.Value).Activate
End Sub
---
Jay

"David" wrote:

> I've got a workbook with several different sheets. I want a macro
> that will select the required sheet depending on what cell is
> highlighted.
>
> For example there are cells for Jan, through to Dec. If the Mar cell
> is highlighted, when the user clicks on the macro button I want it to
> open the sheet for Mar and do some processing. I know how to do the
> processing but how do you change what sheet is active based on this
> kind of user action?
>
> Thanks!
>
>

OK, this works:

sMonth = ActiveCell.Value

Worksheets(sMonth).Activate

Another question, how do I used wildcards in Excel?

For example, if the active cell was "Mar" but the worksheet was Mar
'07, how could I get it to still open the right worksheet?

I tried adding a * after the Worksheets(sMonth) but that didn't work,
any ideas?

The following activates the worksheet whose name starts with whatever is in
the activecell (sMonth):

Private Sub CommandButton1_Click()

sMonth = ActiveCell.Value

For Each ws In Worksheets
If InStr(1, ws.Name, sMonth) = 1 Then ws.Activate
Next 'ws

End Sub
---
Jay

"David" wrote:

> OK, this works:
>
> sMonth = ActiveCell.Value
>
> Worksheets(sMonth).Activate
>
> Another question, how do I used wildcards in Excel?
>
> For example, if the active cell was "Mar" but the worksheet was Mar
> '07, how could I get it to still open the right worksheet?
>
> I tried adding a * after the Worksheets(sMonth) but that didn't work,
> any ideas?
>
>

Take a look at the LIKE operator.

Mike F
"David" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> OK, this works:
>
> sMonth = ActiveCell.Value
>
> Worksheets(sMonth).Activate
>
> Another question, how do I used wildcards in Excel?
>
> For example, if the active cell was "Mar" but the worksheet was Mar
> '07, how could I get it to still open the right worksheet?
>
> I tried adding a * after the Worksheets(sMonth) but that didn't work,
> any ideas?
>

Thanks for the help so far, it's working well!

Another query.

I have worksheets Apr, May, Jun etc.

Sometimes when I go into Jun worksheet for example, I might need to
also check what was in the previous month, is there an easy way of
doing this rather than saying if it's Jun then check May etc etc? Are
worksheets numbered even though they have names?

Thanks again!

Hi David -

Yes, worksheets have an Index property and they are numbered sequentially
from left to right, starting with 1. So, these statements may work for you:

'Check the value in cell A1 of the sheet before the activesheet
MsgBox Worksheets(ActiveSheet.Index - 1).Range("A1")

'Activate the sheet before the activesheet
Worksheets(ActiveSheet.Index - 1).Activate

---
Jay

"David" wrote:

> Thanks for the help so far, it's working well!
>
> Another query.
>
> I have worksheets Apr, May, Jun etc.
>
> Sometimes when I go into Jun worksheet for example, I might need to
> also check what was in the previous month, is there an easy way of
> doing this rather than saying if it's Jun then check May etc etc? Are
> worksheets numbered even though they have names?
>
> Thanks again!
>
>
>