• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 6
  • 5
  • 4
2 Solutions
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now