ExcelBanter - Do Until Loop

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Do Until Loop (https://www.excelbanter.com/excel-programming/306549-do-until-loop.html)

I'd like the following loop to exit when there are no additional sheets
availble. Please help.

Do Until ActiveSheet.Next = False

Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
Selection.Copy
Windows("Agency Prime Gross Plotting by Placement " & MyValue &
".xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
ActiveCell.Offset(22, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Agency Prime Gross Plotting by Placement " & MyValue &
".xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
For b = 1 To 11
Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Agency Prime Gross Plotting by Placement " & MyValue &
".xls").Activate
ActiveCell.Offset(-1, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next b
ActiveCell.Offset(64, -23).Range("A1").Select
Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
ActiveSheet.Next.Select

Loop

Hargrove,

Instead of a do loop, I think you're better off using a For Each Next.
Here's a simple example that excludes the first sheet:

Sub test()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
If sh.Name < "Sheet1" Then
MsgBox sh.Name
End If
Next sh

End Sub

hth,

Doug Glancy
"Hargrove" wrote in message
...
I'd like the following loop to exit when there are no additional sheets
availble. Please help.

Do Until ActiveSheet.Next = False

Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
Selection.Copy
Windows("Agency Prime Gross Plotting by Placement " & MyValue &
".xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
ActiveCell.Offset(22, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Agency Prime Gross Plotting by Placement " & MyValue &
".xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
For b = 1 To 11
Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Agency Prime Gross Plotting by Placement " & MyValue &
".xls").Activate
ActiveCell.Offset(-1, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Next b
ActiveCell.Offset(64, -23).Range("A1").Select
Windows("Primary GBT Prod_Breakdown " & MyValue & ".xls").Activate
ActiveSheet.Next.Select

Loop

If you want to keep your existing code, you could use error trapping to exit
the loop. Notice there are no conditions on either the Do or Loop statements.

Do