Looping through Excel worksheets with vba 1

[Eprice]

Hi,
I have an excel worksheet where they can add new worksheets (2) for each customer. The worksheets will be named according to the cusotmer's name such as Joe-Branch, Joe-State, Lisa-Branch, Lisa-State and so on. I need to create a summary sheet based on what they enter on those sheets so I am going to use a button with vba code to loop though the sheets to get the answers. I don't know how many customers there will be or what the sheet names will be besides the "-Branch" and "-State" part of the name. How do I use a loop to go through the sheets with -Branch in the sheet name to get the answers and store them to a summary sheet's cell. Then I would run the code to loop through each -State sheet to get those totals. I do know how to loop though all sheets in a workbook but not every other one depending on the sheets name.
Thanks
Lisa

 

[SkipVought]

hi,

Your workbook design has severely complicated your quest.

If ALL your Customer data were in ONE TABLE (sheet), it would be a very simple task, either a lookup formula or an aggregation formula, to obtain a Customer's data. NO VBA REQUIRED! Probably take less than 30 seconds!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I agree with Skip, but how about:
[tt]
Sub Macro1()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
Select Case Split(ws.Name, "-")(1)
Case "State"
MsgBox "Your sheet for State is named " & ws.Name
Case "Branch"
MsgBox "Your Branch sheet is named " & ws.Name
End Select
Next ws

End Sub
[/tt]

Have fun.

---- Andy

 

[xlbo]

alternatively

Sub looper()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If InStr(LCase(ws.Name), "state") > 0 Then

MsgBox ws.Name & " is a state sheet"

ElseIf InStr(LCase(ws.Name), "branch") > 0 Then

MsgBox ws.Name & " is a branch sheet"
Else

'not a branch or a state sheet

End If

Next

End Sub

Andy - a couple of points on your code...

Activeworkbook usage can be prone to errors - better to use THISworkbook reference - it will ALWAYS refer to the workbook that the code is invoked from

Also, you are looping through all sheets - not just worksheets - this will include chart sheets (and if there are any, old xl4 macro sheets) which can cause issues sometimes as they can have different properties. Better to use thisowrkbook.WORKsheets



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Andrzejek]

Thanks xlbo, good to know.

Or how about:
[tt]
Dim i As Integer

With ThisWorkbook
For i = 1 To .Sheets.Count
If InStr(1, .Sheets(i).Name, "-") Then
Select Case Split(.Sheets(i).Name, "-")(1)
Case "State"
MsgBox "Your sheet for State is named " & .Sheets(i).Name
Case "Branch"
MsgBox "Your Branch sheet is named " & .Sheets(i).Name
End Select
Else
MsgBox "This sheet is named " & .Sheets(i).Name
End If
Next i
End With

[/tt]

Have fun.

---- Andy

 

[Eprice]

Thanks for all your suggestions. I think I do agree with Skip and when the customer is added to the worksheet I will put all that data into a table and then link to that. It is good to know how to loop through all the worksheets for future use.
Lisa