Dave,

Firstly, many thanks for the prompt response.

I have tried what you have suggested but unfortunately when I run it, I get
a €˜Run-time error 1004 €“ Application-defined or object-defined error.

I have had a look at it puts the word TOTAL in the last row in Column A for
the very first sheet only then gives the above error. The line it seems to
complain about is
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(A:A) €“ 2"

Any suggestions?

Thanks

Pank


"Dave Ramage" wrote:

Pank,

Sub DoStuff()
Dim wks as Worksheet
Dim lLastRow as Long

For Each wks in Activeworkbook.Worksheets
lLastRow = wks.Range("A1").End(xlDown).Row
wks.PageSetup.PrintArea = "$A$1:$K$" & lLastRow
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTROWS(A:A) €“ 2"
Next wks
End Sub

Cheers,
Dave
"Pank" wrote:

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.