using cells property

[bigjim]

I am using excel 2003 and trying to print an area that will vary depending on
the number of players. Here is what I have now.


Dim players As Long
Dim pages As Long
Dim rowcount As Long


players = ActiveWorkbook.Sheets("week 1").Range("ad3").Value
pages = (players / 3)
rowcount = pages * 60

ActiveWorkbook.Sheets("individual stats").Range(Cells(2, 1), Cells(rowcount,
24)).PrintOut Copies:=1, Collate:=True

I am getting "application-defined or object-defined error" with the last
line highlighted. I checked the variable rowcount and it is correct. 900 in
this case.

When I substitute range("a2:x900") for range(cells(2,1),cells(rowcount,24)).
It work great, but it will not always be row 900. It wil vary.
Any help would be appreciated

 

[OssieMac]

You would have found it would work if the active sheet. When you use Cells
you must specify the worksheet or excel thinks you are referring to the
active sheet.

You can either use
Sheets("individual stats").Range(Sheets("individual stats").Cells(2, 1), etc

or better to use With / End with like the following. Note the dot in front
of cells.

Note also that a space and underscore at the end of a line is a line break
in an otherwise single line of code.

With ActiveWorkbook.Sheets("individual stats")
.Range(.Cells(2, 1), _
.Cells(RowCount, 24)) _
.PrintOut Copies:=1, Collate:=True
End With

 

[bigjim]

Thanks, that works. And I really appreciate you helping with why it works,
not just giving me some code to use. I am still in the learning phase and
you have really helped. Thanks a bunch

Jim