I need to write code that automatically moves from sheet to sheet and gathers
data. I have no problem gathering the data on the sheet but I don't know how
to move the pointer to the next sheet in the workbook.
I would really appreciate any help you can give.
--
Bob A

Sub moveSheetToSheet()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
MsgBox ws.Name
Next
End Sub

"Bob A" wrote:

I need to write code that automatically moves from sheet to sheet and gathers
data. I have no problem gathering the data on the sheet but I don't know how
to move the pointer to the next sheet in the workbook.
I would really appreciate any help you can give.
--
Bob A

Are you wanting to loop thru sheets? You can use this.

Sub LoopSheets()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Activate
'do something
Next wks
End Sub

If you are just wanting to activate the next sheet you can explicitly
reference the sheet.

Sheets("Sheet2").Activate
--
Cheers,
Ryan


"Bob A" wrote:

I need to write code that automatically moves from sheet to sheet and gathers
data. I have no problem gathering the data on the sheet but I don't know how
to move the pointer to the next sheet in the workbook.
I would really appreciate any help you can give.
--
Bob A

Hi,

There are several ways, this loops through an array of named sheets

Sub Sonic()
Dim V As Variant
Dim S As String
Dim sh As Worksheet
S = "Sheet1,Sheet2,Sheet3"
V = Split(S, ",")
For Each sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(CStr(sh.Name), V, 0)) Then
'do things
MsgBox sh.Name
End If
Next sh
End Sub


Mike

"Bob A" wrote:

I need to write code that automatically moves from sheet to sheet and gathers
data. I have no problem gathering the data on the sheet but I don't know how
to move the pointer to the next sheet in the workbook.
I would really appreciate any help you can give.
--
Bob A

wow, thank you VERY much for your great help!
--
Bob A


"Mike H" wrote:

Hi,

There are several ways, this loops through an array of named sheets

Sub Sonic()
Dim V As Variant
Dim S As String
Dim sh As Worksheet
S = "Sheet1,Sheet2,Sheet3"
V = Split(S, ",")
For Each sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(CStr(sh.Name), V, 0)) Then
'do things
MsgBox sh.Name
End If
Next sh
End Sub


Mike

"Bob A" wrote:

I need to write code that automatically moves from sheet to sheet and gathers
data. I have no problem gathering the data on the sheet but I don't know how
to move the pointer to the next sheet in the workbook.
I would really appreciate any help you can give.
--
Bob A

Do you see a "YES" button next to Was this post helpful to you?
Someone told me that they don't see it so they can't give credit to those
that help with code. I saw your reply about "Thanks" but not check mark by
our names. I'm just curious.


--
Cheers,
Ryan


"Bob A" wrote:

wow, thank you VERY much for your great help!
--
Bob A


"Mike H" wrote:

Hi,

There are several ways, this loops through an array of named sheets

Sub Sonic()
Dim V As Variant
Dim S As String
Dim sh As Worksheet
S = "Sheet1,Sheet2,Sheet3"
V = Split(S, ",")
For Each sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(CStr(sh.Name), V, 0)) Then
'do things
MsgBox sh.Name
End If
Next sh
End Sub


Mike

"Bob A" wrote:

I need to write code that automatically moves from sheet to sheet and gathers
data. I have no problem gathering the data on the sheet but I don't know how
to move the pointer to the next sheet in the workbook.
I would really appreciate any help you can give.
--
Bob A