If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
auto format 5 worksheets out of 6 when opening excel spreadsheet.
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 Auto_Open() 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 |
#2
|
|||
|
|||
auto format 5 worksheets out of 6 when opening excel spreadsheet.
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 In article , "mike" wrote: 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 Auto_Open() 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 |
Thread Tools | |
Display Modes | |
|
|