Help needed to run code on multipul sheets, based upon sheet name

U

Upex

Board Regular
Joined
Dec 29, 2010
Messages
197
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have the following code that does what I want
Code: 
Sub CompileHistoricEOSScore()
    Sheets("HistoricLog").Range("b2:c1000").ClearContents   
    For Each ws In ActiveWorkbook.Worksheets   
        If ws.Range("a2").Value = "Review of Performance" Then
            For Each c In ws.Range("b40:ay40").cells    
                CourseTitle = c.Offset(-24, 0).Value   
                EOSNumber = c.Value
            If EOSNumber < 0 Then   
                GoTo skipit
            End If
            If CourseTitle = Empty Then
                GoTo skipit
            End If
     Temp = Application.Match(CourseTitle, Sheets("HistoricLog").Range("A1:A100"), 0)   
        Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber   
        Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit:
Next c
        End If
    Next ws
End Sub

However, I have 203 sheets at present and would like to amend this code to function on only the sheets that have a name of "Review of...." Perhaps using a wildcard or >= "Review of". My sheets are Called "Review of 1st Period", "Review of 2nd Period"..... "Review of 39th Period".

How can I change my code to only look at these 39 sheets, rather than all 203 (which will grow). I was thinking that the code of
Code: 
If ws.Range("a2").Value = "Review of Performance" Then

could be amended to something like
Code: 
If ws.name >= "Review of" Then

but this doesnt work. I get a "run time error 13 Type Mis-match" at the following line:
Code: 
Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber

Any help would be great as I'm unsure how to make reference to checking the sheet name. Also, if it helps, the sheets are named in VBA as Review_1, Review_2.....Review_39. I don't mind using the sheet names or the reference names, but again, wouldnt know how to code in the name or reference name.

Thanks in advance, Upex.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
The most simple way, that also involves the most typing would be to just a case statement:
Rich (BB code): 
Sub CompileHistoricEOSScore()
Sheets("HistoricLog").Range("b2:c1000").ClearContents 
 
Select Case ws.Name
Case "Review of 1st Period", "Review of 2nd Period" 'add more sheet names
For Each ws In ActiveWorkbook.Worksheets 
If ws.Range("a2").Value = "Review of Performance" Then
For Each c In ws.Range("b40:ay40").cells 
CourseTitle = c.Offset(-24, 0).Value 
EOSNumber = c.Value
If EOSNumber < 0 Then 
GoTo skipit
End If
If CourseTitle = Empty Then
GoTo skipit
End If
Temp = Application.Match(CourseTitle, Sheets("HistoricLog").Range("A1:A100"), 0) 
Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber 
Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit:
Next c
End If
Next ws
 
Case Else
'do nothing
 
End Select
End Sub
 
You may be able to create an if statement that checks the sheet name using the mid and find functions, however I am afraid I am not sure exactly how to code this. Maybe someone else will be able to help. The above should work though, even if you do have to type in the 30 odd sheet names

Edit: Forgot about the like statement. Why I am not an Excel Guru ;)
 
Last edited:
Upvote 0
VoG,

Once again you save me a lot of brain ache! Thank you. I will have to buy you a drink one day! :beerchug:

Jameo, thanks for your reply also. Due to the less effort required, I'll opt for the like. :-)

Out of interest, if I wanted to base it upon the reference name (the bit not in brackets within vba) what would I change the code to? ws.reference.name or something like that?

Many thanks to both of you.

Regards, Upex
 
Upvote 0
Thanks VoG, you are indeed a Guru.

Perhaps you can offer a little guidance on thsi next one as well.

I have 2 ranges that I need to perform the above actions for, I have been trying to get the code to run on the 1st range (as above) then run on the second, then move to the next sheet. However the second range has a different offset.

I'm currently trying:
Code: 
Sub CompileHistoricEOSScore()
    Sheets("HistoricLog").Range("b2:c1000").ClearContents
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("a2").Value = "Review of Performance" Then
            
            For Each c In ws.Range("b40:ay40")
                coursetitle = c.Offset(-24, 0).Value
                EOSNumber = c.Value
                If EOSNumber < 0 Then
                    GoTo skipit
                End If
                If coursetitle = "Select Planned Course" Then
                    GoTo skipit
                End If
                If coursetitle = Empty Then
                    GoTo skipit
                End If
            
                Next c
            
            For Each c In ws.Range("b73:ay73")
                coursetitle = c.Offset(-16, 0).Value
                EOSNumber = c.Value
                If EOSNumber < 0 Then
                    GoTo skipit
                End If
                If coursetitle = "Select Un-Planned Course" Then
                    GoTo skipit
                End If
                If coursetitle = Empty Then
                    GoTo skipit
                End If
            
            Temp = Application.Match(coursetitle, Sheets("HistoricLog").Range("A1:A100"), 0)
            Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber
            Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit:
        Next c
    
    Next ws
End Sub
and have tried many different tweeks and changes etc, but it's beyond me. Any ideas, suggestions would be awesome.

Thanks, Upex.
 
Upvote 0
You should really try to avoid GoTo but maybe a workaround

Code: 
Sub CompileHistoricEOSScore()
    Sheets("HistoricLog").Range("b2:c1000").ClearContents
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("a2").Value = "Review of Performance" Then
            
            For Each c In ws.Range("b40:ay40")
                coursetitle = c.Offset(-24, 0).Value
                EOSNumber = c.Value
                If EOSNumber < 0 Then
                    GoTo skipit
                End If
                If coursetitle = "Select Planned Course" Then
                    GoTo skipit
                End If
                If coursetitle = Empty Then
                    GoTo skipit
                End If
skipit:
                Next c
            
            For Each c In ws.Range("b73:ay73")
                coursetitle = c.Offset(-16, 0).Value
                EOSNumber = c.Value
                If EOSNumber < 0 Then
                    GoTo skipit2
                End If
                If coursetitle = "Select Un-Planned Course" Then
                    GoTo skipit2
                End If
                If coursetitle = Empty Then
                    GoTo skipit2
                End If
            
            Temp = Application.Match(coursetitle, Sheets("HistoricLog").Range("A1:A100"), 0)
            Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber
            Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit2:
        Next c
    
    Next ws
End Sub
 
Upvote 0
Thanks VoG

I tried the code, but get a compile error: Next without For
on the line
Code: 
Next ws

I dont understand this as we have the line
Code: 
For Each ws In ActiveWorkbook.Worksheets
at the top.

Any ideas?
 
Upvote 0
It was missing an End If

Rich (BB code): 
Sub CompileHistoricEOSScore()
Sheets("HistoricLog").Range("b2:c1000").ClearContents
For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("a2").Value = "Review of Performance" Then
    
        For Each c In ws.Range("b40:ay40")
            coursetitle = c.Offset(-24, 0).Value
            EOSNumber = c.Value
            If EOSNumber < 0 Then
                GoTo skipit
            End If
            If coursetitle = "Select Planned Course" Then
                GoTo skipit
            End If
            If coursetitle = Empty Then
                GoTo skipit
            End If
skipit:
        Next c
        
        For Each c In ws.Range("b73:ay73")
            coursetitle = c.Offset(-16, 0).Value
            EOSNumber = c.Value
            If EOSNumber < 0 Then
                GoTo skipit2
            End If
            If coursetitle = "Select Un-Planned Course" Then
                GoTo skipit2
            End If
            If coursetitle = Empty Then
                GoTo skipit2
            End If
            
            Temp = Application.Match(coursetitle, Sheets("HistoricLog").Range("A1:A100"), 0)
            Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber
            Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit2:
        Next c
    End If
    
Next ws
End Sub
 
Upvote 0
Thanks again VoG. I sincerley hope that your expertise is used in a professional capacity as if its just a personal interest, your skills are wasted!

The code you suggested was 'writing' the data from the first range, so I've changed it to:
Code: 
Sub CompileHistoricEOSScore()
    Sheets("HistoricLog").Range("b2:c100").ClearContents   'Clears the sheet to start collation again
    For Each ws In ActiveWorkbook.Worksheets   'starts looping through sheets
        If ws.CodeName Like "Review_*" Then
            For Each c In ws.Range("b40:ay40") '.Cells   'starts looping through courses
                coursetitle = c.Offset(-24, 0).Value   'finds which course it is
                EOSNumber = c.Value
            If EOSNumber < 0 Then   'Moves on if the cell is blank/does not have a numeric value
                GoTo skipit
            End If
            If coursetitle = Empty Then
                GoTo skipit
            End If
            If coursetitle = "Select Planned Course" Then
                GoTo skipit
            End If
        Temp = Application.Match(coursetitle, Sheets("HistoricLog").Range("A1:A100"), 0)   'Finds row number on EOS log page
        Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber   'Adds the EOS score to the right place and increments the count column
        Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit:
Next c
            For Each c In ws.Range("b73:ay73")
                coursetitle = c.Offset(-16, 0).Value
                EOSNumber = c.Value
            If EOSNumber < 0 Then
                GoTo skipit2
            End If
            If coursetitle = Empty Then
                GoTo skipit2
            End If
            If coursetitle = "Select Un-Planned Course" Then
                GoTo skipit2
            End If
        Temp = Application.Match(coursetitle, Sheets("HistoricLog").Range("A1:A100"), 0)
        Sheets("HistoricLog").Range("b" & Temp).Value = Sheets("HistoricLog").Range("b" & Temp).Value + EOSNumber
        Sheets("HistoricLog").Range("c" & Temp).Value = Sheets("HistoricLog").Range("c" & Temp).Value + 1
skipit2:
Next c
        End If
    Next ws
End Sub

Which is picking up both ranges and writting them to where I need them.

many many thanks for your help, I wouldnt have got here without you!

Many kind regards, Upex
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - Create a new sheet + hyperlink name
Replies
3
Views
429
excelnewbie990
E
S
  • Question Question
VBA Help - Need listbox to scroll to top when opened
Replies
1
Views
603
rlv01
R
M
  • Question Question
Need help saving an already open pdf file using name from spreadsheet through VBA
Replies
6
Views
171
MatthewBiersay
M
H
  • Solved
Double Click Cell to cell to Sheet Name and Cell
2
Replies
11
Views
821
howard
H
N
  • Question Question
Pivot graph data when creating a new tab from a template.
Replies
0
Views
258
Nippyzap
N

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

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
Back
Top