Loop through of sheets and execute calculations based on dynamic sheet references

[suremac]

Greetings,


Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:

Sub LoopthroughWorksheets()
    Dim sheet_name As Range
    Dim sheet_name2 As Range
    Set sheet_name2 = Sheets("WS").Range("F:F")
    For Each sheet_name In Sheets("WS").Range("C:C")
        If sheet_name.Value = "" Then
            Exit For
        Else
            With Sheets(sheet_name.Value)
                    .Range("K1") = .Range("sheet_name2.Value!A14").Value
            End With
        End If
        Next sheet_name
    End Sub

I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:

.Range("K1") = .Range("sheet_name2.Value!A14").Value

Any help is much appreciated.


Regards,

 

[pbornemeier]

I believe this is what you want:

.Range("K1").Value = Worksheets("sheet_name2").Range("A14").Value

 

[suremac]

Thanks for the help pbornemeier. This is the code I was trying to produce.

Sub LoopthroughWorksheets()
    Dim sheet_name As Range
    Dim sheet_name2 As Range
    Set sheet_name2 = Sheets("WS").Range("F:F")
    ' NEW
    Dim counter As Long
    counter = 1


    For Each sheet_name In Sheets("WS").Range("C:C")
        If sheet_name.Value = "" Then
            Exit For
        Else
            With Sheets(sheet_name.Value)
                .Range("K1").Value = Sheets(sheet_name2(counter, 1).Value).Range("A14").Value
                ' NEW                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^                   
                counter = counter + 1
            End With
        End If
    Next sheet_name
End Sub