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

jewkes6000

Board Regular
Joined
Mar 25, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try changing this line:
VBA Code:
If i = "ALL DATA" Then Exit For
to
VBA Code:
If ActiveSheet.Name = "ALL DATA" Then Exit For
or
VBA Code:
If Sheets(i).Name = "ALL DATA" Then Exit For
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Another option
VBA Code:
For i = 2 To Sheets("All Data").index -1
This way you don't need the If statement
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,543
Members
416,923
Latest member
jarri

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
Top