runtime error 1004

  • Thread starter Thread starter JC
  • Start date Start date
J

JC

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
 
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
 
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 -
 
Back
Top