Apply code to all sheets in a workbook [resolved, really]

[thebloke]

Guys, I've written a bit of code that does exactly what I want, that's no problem. What I want though is for the code to apply to every sheet in the workbook from one click of a command button. I know there is a simple way to do this but the syntax just eludes me. I don't really do Excel, or at least I try not to.

Cheers

[thebloke]

I thought I'd resolved this but it doesn't work:

Sub UpdateForecast()
 
Dim w As Integer, x As Integer, y As Integer, z As Integer
Dim firstrow As Integer, firstcol As Integer
Dim colcount As Integer, rowcount As Integer
Dim forecol As Integer
Dim strmonth As String
Dim myArray As Variant
 
For Each sht In ActiveWorkbook.Sheets
 
 
If ActiveSheet.Cells(1, 1) <> "" Then
 
ActiveSheet.Cells(6, 3).Activate
 
myArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
 
    For w = 0 To UBound(myArray)
 
    x = 6
    y = 3
    z = 0
 
    firstrow = x
    firstcol = y
    
    colcount = ActiveCell.CurrentRegion.Columns.Count
    rowcount = ActiveCell.CurrentRegion.Rows.Count
    
    strmonth = myArray(w)
    
    Do Until x = rowcount + 1
    
        If Trim(ActiveSheet.Cells(x, 4)) = strmonth Then
            z = z + ActiveSheet.Cells(x, y)
        End If
        
        x = x + 1
        
    Loop
    
    x = 6
    forecol = 7
    
    Do Until x = rowcount + 1
    
    ActiveSheet.Cells(x, forecol).Activate
    
        If Trim(ActiveSheet.Cells(x, forecol)) = strmonth Then
            ActiveSheet.Cells(x, forecol + 1) = z
        End If
        
        x = x + 1
    
    Loop
    
    ActiveSheet.Cells(6, 3).Activate
    
    Next w
    
    End If
    
Next sht
 
End Sub

Rather, it does work if I step through it and manually activate each sheet as I'm doing so. I think all I need to do is to activate each sheet programatically but I'm not sure how to do this or if I'm barking up the wrong proverbial.

Cheers

[thebloke]

For those interested, this did the trick:

Sub UpdateForecast()
 
Dim v As Integer, w As Integer, x As Integer, y As Integer, z As Integer
Dim firstrow As Integer, firstcol As Integer
Dim colcount As Integer, rowcount As Integer
Dim forecol As Integer, sCount As Integer
Dim strmonth As String
Dim myArray As Variant
 
 
sCount = Worksheets.Count
 
 
v = 1
 
Do Until v = sCount + 1
 
ActiveWorkbook.Sheets(v).Activate
 
If ActiveSheet.Cells(1, 1) <> "" Then
 
ActiveSheet.Cells(6, 3).Activate
 
myArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
 
    For w = 0 To UBound(myArray)
 
    x = 6
    y = 3
    z = 0
 
    firstrow = x
    firstcol = y
    
    colcount = ActiveCell.CurrentRegion.Columns.Count
    rowcount = ActiveCell.CurrentRegion.Rows.Count
    
    strmonth = myArray(w)
    
    Do Until x = rowcount + 1
    
        If Trim(ActiveSheet.Cells(x, 4)) = strmonth Then
            z = z + ActiveSheet.Cells(x, y)
        End If
        
        x = x + 1
        
    Loop
    
    x = 6
    forecol = 7
    
    Do Until x = rowcount + 1
    
        If Trim(ActiveSheet.Cells(x, forecol)) = strmonth Then
            ActiveSheet.Cells(x, forecol + 1) = z
        End If
        
        x = x + 1
    
    Loop
    
    ActiveSheet.Cells(6, 3).Activate
    
    Next w
    
    End If
    
 
v = v + 1
 
Loop
 
ActiveWorkbook.Sheets(1).Activate
 
End Sub

[temped]

Looks confusing enough, one would think it would work. Except when I cut and pasted this code into a module in a new work book something happened but there was no visible output. Strange. I'll keep trying to sort through it.

[thebloke]

Tempted, this works for me. What are you trying to do with it? It'll need modifying to suit your spreadsheet.