auto format 5 worksheets out of 6 when opening excel spreadsheet.

[mike]

to all:

I'm trying to auto format 5 worksheets that are in my
workbook, but don't want the sixth worksheet formated.
So, I want worksheet1 to remain static and worksheet2 -
worksheet6 to be auto formated with my macro when opening
up the excel spreadsheet.

Below is what I've been using for an excel spreadsheet
that has only two worksheets in it:

Public Sub Autpen()
On Error Resume Next ' In case there are no blanks
Columns("A:A").SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 2002
lastrow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
' MsgBox (lastrow)
totalrows = lastrow - 1
lastrow = lastrow + 2
cellm1 = "A" & lastrow
cellm2 = "B" & lastrow
Sheet2.Cells.Range(cellm1, cellm2).Merge
Sheet2.Cells(lastrow, 1).Value = "Total Records"
Sheet2.Cells(lastrow, 3).Value = totalrows
Sheet2.UsedRange.AutoFormat (xlRangeAutoFormatList1)
End Sub

the above macro formats worksheet2 only, how would I
adopt this to automatically format 5 worksheets when
opening up my excel spreadsheet?

Any help would be great and thanks in advance!

Mike

 

[J.E. McGimpsey]

One way:

First, since AutoOpen is deprecated, I'd change this to a
Workbook_Open event macro (put it in the ThisWorkbook code module):

Private Sub Workbook_Open()
Dim i As Long
For i = 2 To Worksheets.Count
With Worksheets(i)
On Error Resume Next
.Columns("A:A").SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
With .Range("A" & Rows.Count).End(xlUp).Offset(2, 0)
.Resize(1, 2).Merge 'generally a bad idea
.Value = "Total Records"
.Offset(0, 2).Value = .Row - 3
End With
.UsedRange.AutoFormat (xlRangeAutoFormatList1)
End With
Next i
End Sub

The editorial comment about merges is due to my finding that they
nearly always cause more trouble than they're worth. YMMV