Where am I going wrong?

[Pank]

I have the following macro, which works fine except for the two lines of code
before the With.PageSetup.

Only the last row in the last worksheet in the workbook is set to bold. All
other sheets have the description TOTAL NUMBER OF SKIPS set in the last row
in Col A and a corresponding count in the last row in column b, but it is not
bolded.

Any suggestions or ideas would be appreciated.


Sub Format()
Dim wks As Worksheet
Dim lLastRow As Long
Application.ScreenUpdating = False

If ActiveSheet.Name <> "MAIN" Then

For Each wks In ActiveWorkbook.Worksheets
With wks

lLastRow = wks.Range("A1").End(xlDown).Row
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL NUMBER OF SKIPS"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(B:B) - 2"
lLastRow = wks.Range("A1").End(xlDown).Row
wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
lLastRow = wks.Range("A1").End(xlDown).Row
Rows(lLastRow).Select
Selection.Font.Bold = True

With .PageSetup

Page set up code

End With
.UsedRange.Rows.AutoFit
End With
Next wks
End If

More code down here.

 

[Jim Rech]

Replace the last two lines with this:

wks.Rows(lLastRow).Font.Bold = True

This will set the font of the last row of each sheet bold (I assume that
what you want).

No selecting is necessary (selecting will not even work anyway since you are
not activating each worksheet.

Also, no need to repeat:

lLastRow = wks.Range("A1").End(xlDown).Row

Once is enough. Also you start With wks and never use it.

--
Jim
|I have the following macro, which works fine except for the two lines of
code
| before the With.PageSetup.
|
| Only the last row in the last worksheet in the workbook is set to bold.
All
| other sheets have the description TOTAL NUMBER OF SKIPS set in the last
row
| in Col A and a corresponding count in the last row in column b, but it is
not
| bolded.
|
| Any suggestions or ideas would be appreciated.
|
|
| Sub Format()
| Dim wks As Worksheet
| Dim lLastRow As Long
| Application.ScreenUpdating = False
|
| If ActiveSheet.Name <> "MAIN" Then
|
| For Each wks In ActiveWorkbook.Worksheets
| With wks
|
| lLastRow = wks.Range("A1").End(xlDown).Row
| wks.Cells(lLastRow + 1, 1).Formula = "TOTAL NUMBER OF SKIPS"
| wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(B:B) - 2"
| lLastRow = wks.Range("A1").End(xlDown).Row
| wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
| lLastRow = wks.Range("A1").End(xlDown).Row
| Rows(lLastRow).Select
| Selection.Font.Bold = True
|
| With .PageSetup
|
| Page set up code
|
| End With
| .UsedRange.Rows.AutoFit
| End With
| Next wks
| End If
|
| More code down here.
|

 

[Pank]

Jim,

Thanks it worked get. Also many thanks for the additional pointers.

Regards

Pank