VBA Code to cycle through worksheets and then stop at specified worksheet

[jewkes6000]

I have a code which goes through all of the worksheets starting from #2 going through ALL of my worksheets. I would like for it to stop at a specified worksheet called "ALL DATA". Here is the code I have thus far. The "If i = "ALL DATA" Then Exit For" line is not working.

ShtCount = ActiveWorkbook.Sheets.Count

For i = 2 To ShtCount
 
Worksheets(i).Activate
If i = "ALL DATA" Then Exit For

lastrow = ActiveSheet.Cells(Rows.Count, "O").End(xlUp).Row
 
Range("M14:Z" & lastrow).Select
 
Selection.Copy
Sheets("PowerBI Data").Activate
 
'LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Select
 
'Required after first paste to shift active cell down one
Range("D1").Select

    Range("D1048576").Select
   Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    
'The following is another way to find the cell which you need to paste to, but it takes much longer for macro to run
    'Do While Not IsEmpty(ActiveCell)
    '    ActiveCell.Offset(1, 0).Select
    'Loop
 
    ActiveCell.Offset(0, -2).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Next i

 

[Joe4]

Welcome to the Board!

Try changing this line:

If i = "ALL DATA" Then Exit For

to

If ActiveSheet.Name = "ALL DATA" Then Exit For

or

If Sheets(i).Name = "ALL DATA" Then Exit For

 

[Fluff]

Another option

For i = 2 To Sheets("All Data").index -1

This way you don't need the If statement

 

[jewkes6000]

Thank you so much! Worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback