ExcelBanter - Help with Macro Please

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Help with Macro Please (https://www.excelbanter.com/excel-discussion-misc-queries/34213-help-macro-please.html)

Help with Macro Please

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.

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.

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.

is your Countrows() a user defined function?
try the macro with just CountA(A:A) in the formula and see what happens

"Pank" wrote:

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.

bj,

Thanks for your suggestion.

Countrows is a function that I founf under Look up and Reference (does that
make it a UDF?)

Secondly, I tried your suggestion of replacing Countrows with CountA and
unfortunately, I get the same result.

Regards

Pank

"bj" wrote:

is your Countrows() a user defined function?
try the macro with just CountA(A:A) in the formula and see what happens

"Pank" wrote:

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.

I have 2002 and did not have countrows()
I do not see anything in your macro which should give this problem.
have you tried running the macro with this line commented out?
have you tried running this macro with the line just above it commented out
have you tried running the macro with a formula in the column A
Have you tried runniing the macro with text in Column B

(In other words, I have no clue why it isn't working)

"Pank" wrote:

bj,

Thanks for your suggestion.

Countrows is a function that I founf under Look up and Reference (does that
make it a UDF?)

Secondly, I tried your suggestion of replacing Countrows with CountA and
unfortunately, I get the same result.

Regards

Pank

"bj" wrote:

is your Countrows() a user defined function?
try the macro with just CountA(A:A) in the formula and see what happens

"Pank" wrote:

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.