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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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
6,431
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
6,431
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
6,431
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,276
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top