help with Loop

can an expert show me how to put the following code into a loop please.

If ActiveSheet.Index = Worksheets.Count Then
 Worksheets(1).Select
 Else
 ActiveSheet.Next.Select
 End If
 
 If ActiveSheet.Name = Event Then
    Exit Sub
    End If
   
 Formulas ' this is a macro to input formulas into each sheet if there is a sheet there
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
I'm not sure what you are trying to do. Please explain without code what you want the code to do.

Kevin
0
Saurabh Singh TeotiaCommented:
I'm assuming you want to select every worksheet in your workbook like you said..You can use the following code to do that...

dim ws as worksheet

for each ws in activeworkbook.worksheets
ws.select
'your code here
next ws

Open in new window


Saurabh...
0
JagwarmanAuthor Commented:
I want to select each worksheet in my workbook, except for my sheet called Event [which is Sheet1]].and then insert formulas into each sheet.

So there can be from 1 to 10 sheets which I need to loop through [excluding my sheet called Event]
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Saurabh Singh TeotiaCommented:
Jagwarman in the above code which i gave to you..if just add one more line in it..it will do what you are looking for...

dim ws as worksheet

for each ws in activeworkbook.worksheets
if UCASE(ws.name) <>"EVENT" THEN  ws.select
'your code here
next ws

Open in new window

0
zorvek (Kevin Jones)ConsultantCommented:
You do not need to select anything to loop through the workbooks and put formulas into cells. Use this code as a template:

Public Sub ApplyFormula()

    Dim TargetWorksheet As Worksheet
    
    For Each TargetWorksheet In ThisWorkbook.Worksheets
        If TargetWorksheet.Name <> "Event" Then
            TargetWorksheet.Range("A1").Formula = "=123"
        End If
    Next TargetWorksheet

End Sub

Open in new window


Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Hi Kevin...How are you doing?.. Whats up..?
0
zorvek (Kevin Jones)ConsultantCommented:
The moon. Wassup with you?
0
Saurabh Singh TeotiaCommented:
My House roof...and then Sun...
0
JagwarmanAuthor Commented:
Saurabh Singh Teotia

it still looks at the sheet Event?
0
Saurabh Singh TeotiaCommented:
It should not. Do you call that sheet event only or even is a part of a bigger name i believe its a part of bigger name.. Then change this line...

if UCASE(ws.name) <>"EVENT" THEN  ws.select

To this...

If InStr(1, ws.Name, "event", vbTextCompare) = 0 Then ws.Select

This will take care of the fact that in the sheet name if it finds event anywhere..it will skip that worksheet

Saurabh...
0
JagwarmanAuthor Commented:
still looks at sheet named event ?? Sorry to be a Nuisance
0
zorvek (Kevin Jones)ConsultantCommented:
What, exactly, is the name of the sheet? Copy the text from the tab and past it here between quotes.

Kevin
0
JagwarmanAuthor Commented:
"Event"
0
zorvek (Kevin Jones)ConsultantCommented:
Use this code (same as I posted above.) It will not fall into the If condition on that sheet:

Public Sub ApplyFormula()

    Dim TargetWorksheet As Worksheet
    
    For Each TargetWorksheet In ThisWorkbook.Worksheets
        If TargetWorksheet.Name <> "Event" Then
            TargetWorksheet.Range("A1").Formula = "=123"
        End If
    Next TargetWorksheet

End Sub

Open in new window


Kevin
0
Saurabh Singh TeotiaCommented:
Jigswan,

It shouldn't be updating that worksheet if you use the line as it's as it will check for that sheet, However it won't select it if i finds the names event in the same...

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.