Sheet Name as Array Value

[airforceone]

this code is from @Fluff VBA Run macros on multiple sheets, not all sheets

Sub Button1_Click()
   Dim ws As Worksheet  
   '** SET The Sheet Names - MUST Reflect Each Sheet Name Exactly!
   WkSheets = Array("ATC1", "BEC1", "DKC1")  
   For Each ws In Sheets(Array("ATC1", "BEC1", "DKC1"))
      ws.Select
      Call CheckGoalSeekRev 'Macro1
   Next ws
End Sub

I used code below to retrieve and store my sheetname to array

sCount = 1
For Each rngCell In Range("A3:A" & Cells(Rows.Count, "C").End(xlUp).Row)
    If rngCell.Value <> "" Then
        arrValue(sCount) = rngCell.MergeArea(1).Value
        sCount = sCount + 1
    End If
Next

is there a way to used my stored array sheetname in the code above?
or is there a different way to utilized my code for the "For Loop" code?

My concept code

FROM

    WkSheets = Array("ATC1", "BEC1", "DKC1")  
    For Each ws In Sheets(Array("ATC1", "BEC1", "DKC1"))

TO

    wsLoop = arrValue()
    For Each ws In Sheets(arrValue())

 

[BSALV]

 s = ""
     For Each rngCell In Range("A3:A" & Cells(Rows.Count, "C").End(xlUp).Row)
          If rngCell.Value <> "" Then s = s & "|" & rngCell.MergeArea(1).Value     'collect all those values with a "|" as separator
     Next
     sp = Split(Mid(s, 2), "|")     'split on that "|" and ignore the 1st

     For Each ws In Sheets(sp)

 

[airforceone]

 s = ""
     For Each rngCell In Range("A3:A" & Cells(Rows.Count, "C").End(xlUp).Row)
          If rngCell.Value <> "" Then s = s & "|" & rngCell.MergeArea(1).Value     'collect all those values with a "|" as separator
     Next
     sp = Split(Mid(s, 2), "|")     'split on that "|" and ignore the 1st

     For Each ws In Sheets(sp)

rt error 9 - subscript out of range

Dim s As String
Dim ws As Worksheet
Dim sp As Variant
 s = ""
     For Each rngCell In Range("A3:A" & Cells(Rows.Count, "C").End(xlUp).Row)
          If rngCell.Value <> "" Then s = s & "|" & rngCell.MergeArea(1).Value     'collect all those values with a "|" as separator
     Next
     sp = Split(Mid(s, 2), "|")     'split on that "|" and ignore the 1st

     For Each ws In Sheets(sp)
    
     Next

 

[bebo021999]

For each ws in sheets
    For i = 1 to ubound(arrValue)
       If ws.name like arrValue(i) then
            ---put the code here
       end if
    Next
Next

 

[dmt32]

Hi,
updating your approach a little maybe this will do what you want

Dim arrValue() As Variant

Sub DisplayArrayElements()
    Dim ws As Worksheet
  
    For Each ws In Sheets(arrValue)
        MsgBox ws.Name
    Next ws

End Sub


Sub LoadArray()
    Dim sCount  As Long
    Dim rngCell As Range
  
    For Each rngCell In Range("A3:A" & Cells(Rows.Count, "C").End(xlUp).Row)
        If rngCell.Value <> "" Then
            sCount = sCount + 1
            ReDim Preserve arrValue(1 To sCount)
            arrValue(sCount) = rngCell.MergeArea(1).Value
        End If
    Next
  
End Sub

code is just an example idea & does not check if sheet exists or if array has been intilalized

Dave

 

[airforceone]

For each ws in sheets
    For i = 1 to ubound(arrValue)
       If ws.name like arrValue(i) then
            ---put the code here
       end if
    Next
Next

thanks did the job with a little tweak...

 

[airforceone]

Hi,
updating your approach a little maybe this will do what you want

Dim arrValue() As Variant

Sub DisplayArrayElements()
    Dim ws As Worksheet
 
    For Each ws In Sheets(arrValue)
        MsgBox ws.Name
    Next ws

End Sub


Sub LoadArray()
    Dim sCount  As Long
    Dim rngCell As Range
 
    For Each rngCell In Range("A3:A" & Cells(Rows.Count, "C").End(xlUp).Row)
        If rngCell.Value <> "" Then
            sCount = sCount + 1
            ReDim Preserve arrValue(1 To sCount)
            arrValue(sCount) = rngCell.MergeArea(1).Value
        End If
    Next
 
End Sub

code is just an example idea & does not check if sheet exists or if array has been intilalized

Dave

works well, I hope I can marked twice
marked @bebo021999 for the solution since he first replied! but thank you anyway

 

[dmt32]

works well, I hope I can marked twice
marked @bebo021999 for the solution since he first replied! but thank you anyway

no worries - glad we were able to help & appreciate your feedback

Dave