Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Where am I going wrong?
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. |
#2
|
|||
|
|||
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 "Pank" wrote in message ... |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. | |
#3
|
|||
|
|||
Jim,
Thanks it worked get. Also many thanks for the additional pointers. Regards Pank "Jim Rech" wrote: 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 "Pank" wrote in message ... |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. | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's wrong with my array formula | Excel Discussion (Misc queries) | |||
Wrong date on my posts. | Excel Discussion (Misc queries) | |||
What is Wrong with this formula please? | New Users to Excel | |||
What's wrong with this vb? | Excel Worksheet Functions | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) |