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.:rolleyes:

[thebloke]

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