runtime error 1004

Dim ws2 As Worksheet
Dim ws2R As Integer
Set ws2 = Worksheets("PDFs")
ws2R = 20
ws2.Range(Cells(1, 1), Cells(ws2R, 9)).Select

In the code above I always get runtime error 1004 on the last line.
PDFs is a valid sheet name.
Can someone please tell me why this does not work?
Thank you
- jc -

Hi JC

The code is OK if PDFs is a active sheet.
if is not try

Dim ws2 As Worksheet
Dim ws2R As Integer
Set ws2 = Worksheets("PDFs")
ws2R = 20
ws2.select
ws2.Range(Cells(1, 1), Cells(ws2R, 9)).Select

"JC" <jcridgeway@gmail.com> wrote in message
news:1131068720.253318.235130@z14g2000cwz.googlegroups.com...
>
>
> Dim ws2 As Worksheet
> Dim ws2R As Integer
> Set ws2 = Worksheets("PDFs")
> ws2R = 20
> ws2.Range(Cells(1, 1), Cells(ws2R, 9)).Select
>
> In the code above I always get runtime error 1004 on the last line.
> PDFs is a valid sheet name.
> Can someone please tell me why this does not work?
> Thank you
> - jc -
>

Cells will return to the active sheet unless you specify otherwise. You also
need to select the shet if it is not active. Give this a try...

Dim ws2 As Worksheet
Dim ws2R As Integer
Set ws2 = Worksheets("PDFs")
ws2R = 20
with ws2
..select
..Range(.Cells(1, 1), .Cells(ws2R, 9)).Select
end with

--
HTH...

Jim Thomlinson


"JC" wrote:

>
>
> Dim ws2 As Worksheet
> Dim ws2R As Integer
> Set ws2 = Worksheets("PDFs")
> ws2R = 20
> ws2.Range(Cells(1, 1), Cells(ws2R, 9)).Select
>
> In the code above I always get runtime error 1004 on the last line.
> PDFs is a valid sheet name.
> Can someone please tell me why this does not work?
> Thank you
> - jc -
>
>

Carlos & Jim,
You were both right! Thank you! - that allowed me to select the
required cells; however, maybe you can clear something up for me. In
the code of my macro the previous line reads:
ws2.Cells.Clear
and I execute that command while WS1 sheet is active. Is this "need to
be on the active sheet" dependent on which command you are trying to
execute (it appears to be) and if so is there any publication that will
tell me which command requires the sheet to be active?

Normally when I write macros involving more than one sheet I try to
limit the number of times that the screen flashes between the different
sheets and that is what I was trying to prevent in my original lines of
code.
Thank you again for such a quick and accurate response -
- jc -