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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try a For Each loop:

Code:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'
'code goes here
'
Next ws
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top