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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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
 

Forum statistics

Threads
1,089,491
Messages
5,408,592
Members
403,216
Latest member
Boba Fetts

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top