Prev: macro alternate conditional format change
Next: Export multiple Excel sheets to a single text file
From: bigjim on 12 Feb 2010 21:45
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
From: OssieMac on 13 Feb 2010 03:30
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

--
Regards,

OssieMac


From: bigjim on 13 Feb 2010 08:47
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

"OssieMac" wrote:

> 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
>
> --
> Regards,
>
> OssieMac
>
>
 | 
Pages: 1
Prev: macro alternate conditional format change
Next: Export multiple Excel sheets to a single text file