Did you change the cell references?
For j = 1 To z
For i = 1 To z
MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("C1:C" & Worksheets(j).Cells(Rows.Count, "C").End(xlUp).Row))
j = j + 1
Next i
Next j
In your case, I think this should be
For j = 1 To z
For i = 1 To z
MyArray(i) = WorksheetFunction.Sum(Worksheets(j).Range("AD1:AD" & Worksheets(j).Cells(Rows.Count, "AD").End(xlUp).Row))
j = j + 1
Next i
Next j
And if you say now that it needs to be entered in all sheets, change
For k = 2 To Worksheets.Count '<---- Change the 2 to a 1 for all the sheets
With Sheets(k)
'In the following line, it looks at the fifth cell down to determine the last Column. Change the 5 to _
a 1 if you want to determine the last Column from the top cell.
Set LastColumn = Sheets(k).Cells(5, Sheets(k).Columns.Count).End(xlToLeft).Columns.Offset(, 1)
'If you want to paste the subtotals starting at the fifth cell down, change the next line to _
LastColumn.Cells(5).Resize(UBound(MyArray)) = Application.Transpose(MyArray)
'LastColumn.Cells.Resize(UBound(MyArray)) = Application.Transpose(MyArray)
End With
Next k
Clear as mud?