Ending Loop

tcnt9176

Board Regular
Joined
Jun 23, 2008
Messages
223
Ok. This is my 2nd time to ask about ending a loop. I got help with the 1st & it works great!! I tried using the template of that one to make my next one work without success. The macro repeats the same action on multiple worksheets and I need it to stop and go to the next step when there are no other sheets. Here is what I currently have.

Application.ScreenUpdating = False
Do
If Not ActiveSheet.Next.Select Is Nothing Then----This doesn’t work.
Cells.Select
Selection.Copy
ActiveSheet.Next.Select
X
X
X
X
X
Else
Exit Do
End If
Loop
Application.ScreenUpdating = True
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try a For Each loop:

Code:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'
'code goes here
'
Next ws
 

tcnt9176

Board Regular
Joined
Jun 23, 2008
Messages
223
Does it matter that I am not wanting this in all spreadsheets. I am using ActiveSheet.Next.Select so that it doesnt select all sheets. At this point it is not going to the next step after the last worksheet and I wondered if it was b/c I have 17 worksheets but only want it to paste on 12 of them. Here is what I am doing. I have a table with several formulas that calculates off of one sheet of detail. The #s go on each spreadsheet depending on the date in one of the columns. I am copying the table (with formulas) & pasting it to multiple sheets. When I tried copying & pasting it to the other 12 sheets at once. The formulas were not calculating correctly so I decided I would have to paste them one at a time and that works but it creates this issue of not ending the loop.
Thanks Erik for the suggestion on learning more about looping. I will definitely take a look at that. I don't if you remember me from my other issue but if you do, you know that I am limited on what I know. Is there a course that I could take or a book you recommend to learn the basics. At this point I am just learning as I go. I don't really know code; I just record and edit the best I can at this point.
Thanks,
Chris
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
What are the names of the sheets that you don't want to include?
 

tcnt9176

Board Regular
Joined
Jun 23, 2008
Messages
223
Good idea!!!
Placement Report
Placement Detail
Sheet1 Multiple Line Claims
2009-03
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Good idea!!!" And ws.Name <> "Placement Report" And ws.Name <> "Placement Detail" And _
    ws.Name <> "!Sheet1 Multiple Line Claims" And ws.Name <> "2009-03" Then
'
'code goes here
'
    End If
Next ws
 

tcnt9176

Board Regular
Joined
Jun 23, 2008
Messages
223
It still does not skip to the next line. I think I am just going to have to chalk it up to user error. I am not sure what I am doing wrong. I plugged it in and it ran but just didn't go to the next line once it pasted the information to the last worksheet. I looped again.
 
L

Legacy 68668

Guest
Rich (BB code):
Sub test()
Dim ws As Worksheet
For Each ws In Sheets
    Select Case ws.Name
        Case "Placement Report", "Placement Detail", _
               "Sheet1 Multiple Line Claims", "2009-03"
        Case Else
            ' your code here
    End Select
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,089,930
Messages
5,411,323
Members
403,360
Latest member
petroes

This Week's Hot Topics

Top