I recently, found that I needed a mechanism to count the number of rows that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page formatting.

Within the format macro I inserted the following:- (thank you to Dave Ramage)

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"
€˜the €“2 at the end is to discard one header line, and the countrows line
Next wks
End Sub

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, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It 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 would be most welcome

Regards

Pank

Assuming that you have a function called COUNTROWS, try this#

ub 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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pank" wrote in message
...
I recently, found that I needed a mechanism to count the number of rows
that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is
used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page formatting.

Within the format macro I inserted the following:- (thank you to Dave
Ramage)

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"
'the -2 at the end is to discard one header line, and the countrows line
Next wks
End Sub

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, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It 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 would be most welcome

Regards

Pank

Bob,

Thanks for the quick response.

Apart from the first line SUB being relaced by UB, I cannot see any
difference. Where do I insert this code? (P.S. what does UB stand for?)

Thanks

Pank

"Bob Phillips" wrote:

Assuming that you have a function called COUNTROWS, try this#

ub 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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pank" wrote in message
...
I recently, found that I needed a mechanism to count the number of rows
that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is
used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page formatting.

Within the format macro I inserted the following:- (thank you to Dave
Ramage)

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"
'the -2 at the end is to discard one header line, and the countrows line
Next wks
End Sub

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, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It 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 would be most welcome

Regards

Pank

My reply dropped the S. Put it back in :-).

Did you try it?

--
HTH

Bob Phillips

"Pank" wrote in message
...
Bob,

Thanks for the quick response.

Apart from the first line SUB being relaced by UB, I cannot see any
difference. Where do I insert this code? (P.S. what does UB stand for?)

Thanks

Pank

"Bob Phillips" wrote:

Assuming that you have a function called COUNTROWS, try this#

ub 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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pank" wrote in message
...
I recently, found that I needed a mechanism to count the number of
rows
that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is
used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page
formatting.

Within the format macro I inserted the following:- (thank you to Dave
Ramage)

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"
'the -2 at the end is to discard one header line, and the countrows
line
Next wks
End Sub

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, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It 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 would be most welcome

Regards

Pank

Bob,

As always it works as expected.

Many Thanks for you help and quick response.

Regards

Pank



"Bob Phillips" wrote:

My reply dropped the S. Put it back in :-).

Did you try it?

--
HTH

Bob Phillips

"Pank" wrote in message
...
Bob,

Thanks for the quick response.

Apart from the first line SUB being relaced by UB, I cannot see any
difference. Where do I insert this code? (P.S. what does UB stand for?)

Thanks

Pank

"Bob Phillips" wrote:

Assuming that you have a function called COUNTROWS, try this#

ub 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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pank" wrote in message
...
I recently, found that I needed a mechanism to count the number of
rows
that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I wanted.

I decided that I would insert the functionality into a macro, which is
used
on a quarterly basis that basically, merges three sheets, then creates
individual sheets for each company and finally undertakes Page
formatting.

Within the format macro I inserted the following:- (thank you to Dave
Ramage)

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"
'the -2 at the end is to discard one header line, and the countrows
line
Next wks
End Sub

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, and word TOTAL is inserted in the last row in
Column A for the very first sheet only. It 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 would be most welcome

Regards

Pank

Pank,

For your info, it was the hyphen. Somehow your dash wasn't the correct one,
no idea why.

--
HTH

Bob Phillips

"Pank" wrote in message
...
Bob,

As always it works as expected.

Many Thanks for you help and quick response.

Regards

Pank



"Bob Phillips" wrote:

My reply dropped the S. Put it back in :-).

Did you try it?

--
HTH

Bob Phillips

"Pank" wrote in message
...
Bob,

Thanks for the quick response.

Apart from the first line SUB being relaced by UB, I cannot see any
difference. Where do I insert this code? (P.S. what does UB stand
for?)

Thanks

Pank

"Bob Phillips" wrote:

Assuming that you have a function called COUNTROWS, try this#

ub 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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pank" wrote in message
...
I recently, found that I needed a mechanism to count the number of
rows
that
existed in each worksheet.

I discovered that the COUNTROWS functionality was just what I
wanted.

I decided that I would insert the functionality into a macro,
which is
used
on a quarterly basis that basically, merges three sheets, then
creates
individual sheets for each company and finally undertakes Page
formatting.

Within the format macro I inserted the following:- (thank you to
Dave
Ramage)

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"
'the -2 at the end is to discard one header line, and the
countrows
line
Next wks
End Sub

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, and word TOTAL is inserted in the last
row in
Column A for the very first sheet only. It 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 would be most welcome

Regards

Pank