I have recorded the following macro that will set the print area for a
particular sheet:-

Sub Pset()
Range("A1:K1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$14"
End Sub

What I want is to automate this process for all sheets within a workbook.

What I intending to do was:-

For Each wks In ActiveWorkbook.Worksheets
Range("A1:K1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$14"

As the print area within each sheet is different how do I replace the
"$A$1:$K$14" that is currently in the macro?


Additionally, having set the print area, I want to add the word TOTAL in the
last used cell +1 in Column A and the following formula in the adjacent cell
in column B =COUNTROWS(A:A) €“ 2. Again this needs to be done on all sheets
within the book.

Your help is appreciated.