moving between sheets

[aschneid]

Hi guys!

Just a quick question:

I would like to write a function in VBA which would allow me to mov
between the sheets in excel.

Does anyone know the method for it?

Thank yo

 

[CLR]

You can just record a macro, and then assign it to a drawing
object..........Tools > Macro > Record a macro............and then do the
same thing on the sheet you jump to in order to get back to your main
sheet.............

Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

What does "move between sheets" mean to you?

Does it mean to activate first one sheet, then another specific sheet?

Public Sub test1()
If ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet2").Activate
Else
Sheets("Sheet1").Activate
End If
End Sub

To activate all sheets sequentially in the workbook?

Public Sub test2()
If ActiveSheet.Index = Sheets.Count Then
Sheets(1).Activate
Else
Sheets(ActiveSheet.Index + 1).Activate
End If
End Sub


To perform an action on more than one sheet, but not activate them?

Public Sub test3()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
'Do something
Next wsSheet
End Sub

Something else entirely?

Did you really mean *function*? Worksheet functions, including User
Defined Functions can only return values to their calling cells - they
can't activate or select other cells, change the value of another cell,
or change formatting, etc.

 

[David McRitchie]

Hi aschneid,

Take a look at (I just added the second part recently)
Navigating within a Sheet and Around a Workbook
http://www.mvps.org/dmcritchie/excel/navigation.htm#anothersheet

 

[Guest]

The easiest way to find out what VBA function you shoudl
use is to create a macro first then edit the macro and
find out what the VB code is behind it. In your case you
would go to Tools -> Macro -> Record macro. Give it a name
then hit "OK". When you see the box with the stop button
appear move to the work sheet and cell of your choice.
Click the stop button. Then go to Tools -> Macro -> Macros
and click on the edit button. There is the VB for your
worksheet jump. This works great for other VB function
building. Good luck.

 

[Lady Layla]

Why not just use the Ctrl Key + Pge up key or Pge Down key


: The easiest way to find out what VBA function you shoudl
: use is to create a macro first then edit the macro and
: find out what the VB code is behind it. In your case you
: would go to Tools -> Macro -> Record macro. Give it a name
: then hit "OK". When you see the box with the stop button
: appear move to the work sheet and cell of your choice.
: Click the stop button. Then go to Tools -> Macro -> Macros
: and click on the edit button. There is the VB for your
: worksheet jump. This works great for other VB function
: building. Good luck.
: >-----Original Message-----
: >Hi guys!
: >
: >Just a quick question:
: >
: >I would like to write a function in VBA which would allow
: me to move
: >between the sheets in excel.
: >
: >Does anyone know the method for it?
: >
: >Thank you
: >
: >
: >---
: >
: >
: >.
: >