Macro Not Running Across All Sheets

[not so excel-lent]

I have a workbook that has 22 worksheets that are all formatted the same. Each has a data validation list in H3 and I am trying to set up a macro that will select one of the items in the list (in this case "Prior Week"). I would like to run the macro on all sheets. Below is the code I have entered, but after selecting all sheets it only runs the macro on the first sheet ("CA2"). I'm not very experienced with VBA, and I pieced this code together from various responses to other questions. Any suggestions on how I can get it to run on all sheets?

Sub PWonAllsheets()
'
Sheets(Array("CA2", "CA3", "CA4", "CA5", "CA6", "CA7", "CA8", "CA9", "CA15", "CA18", _
"CA27", "AZ_NV", "FL", "GA", "IL", "MI", "NJ", "NY", "OK", "PA", "TX", "VA")).Select
'
Dim pw As String
pw = "Prior Week"
Range("H3") = pw
End Sub

Thanks in advance for any help!
Larry

 

[Ruddles]

I wouldn't have expected that to work. If you select a range of cells on a sheet and type in a word, it only goes into the first cell - yes?

Maybe this:-

Option Explicit
 
Sub PWonAllsheets()

  Dim ws As Worksheet
  Dim pw As String
 
  pw = "Prior Week"

  For Each ws In Sheets
    ws.Range("H3") = pw
  Next ws

End Sub

 

[Ruddles]

Or if you wanted to keep the list approach:-

[FONT=Courier New]Option Explicit[/FONT]
 
[FONT=Courier New]Sub PWonAllsheets()[/FONT]
 
[FONT=Courier New] Dim sheetlist As Variant[/FONT]
[FONT=Courier New] Dim ws As Variant[/FONT]
 
[FONT=Courier New] sheetlist = Array("CA2", "CA3", "CA4", "CA5", "CA6", "CA7", _[/FONT]
[FONT=Courier New]    "CA8", "CA9", [/FONT][FONT=Courier New]"CA15", "CA18", [/FONT][FONT=Courier New]"CA27", "AZ_NV", "FL", _[/FONT]
[FONT=Courier New]    "GA", "IL", "MI", "NJ", "NY", "OK", "PA", "TX", "VA")[/FONT]
 
[FONT=Courier New] Dim pw As String[/FONT]
 
[FONT=Courier New]  pw = "Prior Week"[/FONT]
 
[FONT=Courier New]  For Each ws In sheetlist[/FONT]
[FONT=Courier New]    Sheets(ws).Range("H3") = pw[/FONT]
[FONT=Courier New]  Next ws[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

 

[not so excel-lent]

Worked like a charm!!

I'm a novice at this stuff, so thank you so much for your help!

 

[shg]

Another way. If it's all the sheets,

Sub PWonAllsheets()
    Range("H3").Value = "Prior Week"
    Worksheets.FillAcrossSheets Range("H3")
End Sub

If only those you listed,

Sub PWonAllsheets()
    Range("H3").Value = "Prior Week"
    Worksheets(Array("CA2", "CA3", "CA4", "CA5", "CA6", "CA7", _
                     "CA8", "CA9", "CA15", "CA18", "CA27", "AZ_NV", _
                     "FL", "GA", "IL", "MI", "NJ", "NY", _
                     "OK", "PA", "TX", "VA")).FillAcrossSheets Range("H3")
End Sub

 

[not so excel-lent]

Thanks!

 

[Trebor76]

Hi there,

Here's my attempt that checkes if the cell has data validation and then that the value being attempted to be put into the cell actually matches an item from the data validation list i.e. putting a value directly into a via VBA cell ignores the cell's data validation:

Sub Macro2()

    Dim varDataValItem As Variant, _
        varDataValExists As Variant
    Dim intMyArrayCount As Integer
    Dim strDataValItem As String
    
    varDataValExists = ""
    
    strDataValItem = "Prior Week"
        
    For Each Worksheet In ActiveWorkbook.Worksheets
    
        Sheets(Worksheet.Name).Select
    
        On Error Resume Next
        
            varDataValExists = Range("H3").Validation.Type
        
        On Error GoTo 0
            
        If varDataValExists <> "" Then
            
            'Splits data validation items by a comma as items in a data validation list are separated by a comma.
            varDataValItem = Split(Range("H3").Validation.Formula1, ",")
        
            For intMyArrayCount = 0 To UBound(varDataValItem)
            
                'Only change the value of the cell if it matches one of the _
                items in the data validation for that cell.
                If strDataValItem = varDataValItem(intMyArrayCount) Then
                    
                    Range("H3").Value = strDataValItem
                    
                    Exit For
                
                End If
                
            Next intMyArrayCount
            
        End If
        
        varDataValExists = ""
        
    Next Worksheet
    
End Sub

Regards,

Robert

 

[Ruddles]

Worksheets.[COLOR=blue][B]FillAcrossSheets[/B][/COLOR] Range("H3")

The problem with these really useful methods is that there's no way you can know about them all until you're fortunate enough to overhear someone else talking about them!