Need help with a macro that run another macro on all tabs except 2.

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Hello I am trying to create a macro that will run another macro on all tabs EXCEPT the 1st tab named totals (Sheet1) and last tab named Stats (Sheet44). I have no clue how to do this. Here is a macro that will run a macro on ALL tabs but I am not sure how to alter it to make it exclude tabs.

Code:
      Sub WorksheetLoop2()


         ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet


         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets


            ' Insert your code here.
            ' This line displays the worksheet name in a message box.
            MsgBox Current.Name
         Next


      End Sub

Any help would be greatly appreciated!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Use the following (look up help pages): "IF" statement, worksheet.name property
 

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Use the following (look up help pages): "IF" statement, worksheet.name property

Ok I tried adding that then and I still can't get this macro to work. It just runs the macro 3 times on the same active page.

Code:
Sub UpdateCharts1()
  
 For Each ws In ThisWorkbook.Sheets
        If (ws.Name <> "ATL") And (ws.Name <> "BHM") And (ws.Name <> "CGO") Then
        
    ActiveSheet.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Name = "=""Oct"""
    ActiveChart.SeriesCollection(6).Values = "='" & ActiveSheet.Name & "'!$AC$26:$AC$28"
    Range("AM32").Select
    
        End If
    Next


End Sub

Any suggestions on how to get this to work?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,344
Change ActiveSheet to ws.

Instead of the For Each and If statement, to loop through all sheets except the first and last you could use:
Code:
Sub WorksheetLoop2()

    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
       MsgBox Worksheets(i).Name
    Next

End Sub
 

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149

ADVERTISEMENT

Change ActiveSheet to ws.

Instead of the For Each and If statement, to loop through all sheets except the first and last you could use:
Code:
Sub WorksheetLoop2()

    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
       MsgBox Worksheets(i).Name
    Next

End Sub


Hey I've tried to modify your macro to work with mine but I keep getting another error (sorry I am not very good with VBA yet). I run the macro and it says "Run Time Error: Object Required".

Here is the macro I tried to use:

Code:
Sub WorksheetLoop2()


    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
       MsgBox Worksheets(i).Name
      
    ws.Select
    ws.ChartObjects("Chart 1").Activate
    ws.SeriesCollection(1).Delete
    ws.SeriesCollection.NewSeries
    ws.SeriesCollection(6).Name = "=""Nov"""
    ws.SeriesCollection(6).Values = "='" & ActiveSheet.Name & "'!$AC$26:$AC$28"
    Range("AM32").Select
      
    Next
      




End Sub
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,344
Try one of these (untested, but based on your code). Do you see the difference between them?
Code:
Sub WorksheetLoop3()

    Dim i As Integer, ws As Worksheet
    
    For i = 2 To Worksheets.Count - 1
        MsgBox Worksheets(i).Name
        Set ws = Worksheets(i)
        ws.Select
        ws.ChartObjects("Chart 1").Activate
        ws.SeriesCollection(1).Delete
        ws.SeriesCollection.NewSeries
        ws.SeriesCollection(6).Name = "=""Nov"""
        ws.SeriesCollection(6).Values = "='" & ws.Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next

End Sub

Sub WorksheetLoop4()

    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
        MsgBox Worksheets(i).Name
        Worksheets(i).Select
        Worksheets(i).ChartObjects("Chart 1").Activate
        Worksheets(i).SeriesCollection(1).Delete
        Worksheets(i).SeriesCollection.NewSeries
        Worksheets(i).SeriesCollection(6).Name = "=""Nov"""
        Worksheets(i).SeriesCollection(6).Values = "='" & Worksheets(i).Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next

End Sub
 

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149

ADVERTISEMENT

Try one of these (untested, but based on your code). Do you see the difference between them?
Code:
Sub WorksheetLoop3()

    Dim i As Integer, ws As Worksheet
    
    For i = 2 To Worksheets.Count - 1
        MsgBox Worksheets(i).Name
        Set ws = Worksheets(i)
        ws.Select
        ws.ChartObjects("Chart 1").Activate
        ws.SeriesCollection(1).Delete
        ws.SeriesCollection.NewSeries
        ws.SeriesCollection(6).Name = "=""Nov"""
        ws.SeriesCollection(6).Values = "='" & ws.Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next

End Sub

Sub WorksheetLoop4()

    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
        MsgBox Worksheets(i).Name
        Worksheets(i).Select
        Worksheets(i).ChartObjects("Chart 1").Activate
        Worksheets(i).SeriesCollection(1).Delete
        Worksheets(i).SeriesCollection.NewSeries
        Worksheets(i).SeriesCollection(6).Name = "=""Nov"""
        Worksheets(i).SeriesCollection(6).Values = "='" & Worksheets(i).Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next

End Sub

Hey John, sorry but I can't see the difference (probably because I have very limited knowledge of VBA). But I have tried the following macro and I am still getting an error.


Code:
Sub WorksheetLoop4()


    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
        MsgBox Worksheets(i).Name
        Worksheets(i).Select
        Worksheets(i).ChartObjects("Chart 1").Activate
        Worksheets(i).SeriesCollection(1).Delete
        Worksheets(i).SeriesCollection.NewSeries
        Worksheets(i).SeriesCollection(6).Name = "=""Nov"""
        Worksheets(i).SeriesCollection(6).Values = "='" & Worksheets(i).Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next


End Sub

The error its giving me is Run-time error '4383: Object doesn't support this property or method.

Then the debugger takes me to

Code:
      Worksheets(i).SeriesCollection(1).Delete


Thank you so much for all of your help!
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,344
The difference is that the first uses the ws object (obtained by Set ws = Worksheets(i)), and the second uses the Worksheets array directly - Worksheets(i) to reference a specific sheet.

I didn't notice before, but you also changed ActiveChart to ws, hence the error. Try something like this:
Code:
Sub UpdateCharts2()
  
    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
        Worksheets(i).ChartObjects("Chart 1").Activate
        ActiveChart.SeriesCollection(1).Delete
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(6).Name = "=""Oct"""
        ActiveChart.SeriesCollection(6).Values = "='" & Worksheets(i).Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next

End Sub
 

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
The difference is that the first uses the ws object (obtained by Set ws = Worksheets(i)), and the second uses the Worksheets array directly - Worksheets(i) to reference a specific sheet.

I didn't notice before, but you also changed ActiveChart to ws, hence the error. Try something like this:
Code:
Sub UpdateCharts2()
  
    Dim i As Integer
    
    For i = 2 To Worksheets.Count - 1
        Worksheets(i).ChartObjects("Chart 1").Activate
        ActiveChart.SeriesCollection(1).Delete
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(6).Name = "=""Oct"""
        ActiveChart.SeriesCollection(6).Values = "='" & Worksheets(i).Name & "'!$AC$26:$AC$28"
        Range("AM32").Select
    Next

End Sub


Hey John that worked great! Thank you so so much!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

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