On 15/07/2010 02:19, Excel Hates Me wrote:

Hi EHM,

For a quick and dirty fix you could put ws.select after "For Each ws in
MySheets".

However the code would benefit from being re-written to avoid using
select statements.

I'm sure someone here will provide that code!

Rgds,

MM



Sub AllSheetFunctions()

' select all sheets
Dim myArray() As Variant
Dim i As Integer
For i = 1 To Sheets.count
ReDim Preserve myArray(i - 1)
myArray(i - 1) = i
Next i
Sheets(myArray).Select

' begin repeat for all worksheets
Dim ws As Worksheet
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.select
' Autofit
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Columns.autofit

' Go to next blank row in Column G
Range("G1").End(xlDown).Offset(1, 0).Select

' Bold cell and add text
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Count"

' Move one cell to right and bold
Selection.Offset(0, 1).Select
Selection.Font.Bold = True

' Add formula to blank cell at bottom of column 8
Dim LastRow As Long
LastRow = Range("H65536").End(xlUp).Row + 1
Cells(LastRow, 8).Formula = "=COUNTA(H2:H"& LastRow - 1& ")"

' Move eight cells to right, bold and add text
Selection.Offset(0, 8).Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Totals"

' Move one cell to right, bold, and add formula to blank cell
at bottom of column
Selection.Offset(0, 1).Select
Selection.Font.Bold = True
LastRow = Range("Q65536").End(xlUp).Row + 1
Cells(LastRow, 17).Formula = "=SUM(Q2:Q"& LastRow - 1& ")"

Next ws

' end repeat for all worksheets

End Sub