Run-time error '9': Subscript out of range

[exceltadpole]

New to Excel. Trying to take copy a large data (values) from another workbook into current sheet with this code:

'--------------------------------

' open the source workbook and select the source sheet
Workbooks.Open Filename:="y:icontrol.xls"
Sheets("invSheet").Select

' copy the source range
Sheets("invSheet").Range("F4:I37150").Select
Selection.Copy

' select current workbook and paste the values starting at U4
Sheets("pnumSheet").Select
Sheets("pnumSheet").Range("U4:X37150").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' close the source workbook
Windows("icontrol.xls").Activate
ActiveWorkbook.Close

'--------------------------------

But at - Sheets("pnumSheet").Select
It gives
Run-time error '9':
Subscript out of range


The pnumSheet was already open and it contains the above code.
How this can be fixed? Please help.

The goal is to have the data values copied
from workbook icontrol.xls sheet invSheet range F4:I37150
to workbook qsys.xls sheet pnumSheet range U4:X37150

 

[Tom Urtis]

It might be due to this line being incorrect:
Workbooks.Open Filename:="y:icontrol.xls"
because the filename cannot have a colon in it. Maybe you are missing a slash if it's off a Y drive (?) Also look very closely at the sheet tab of pnumSheet and make sure the spelling is EXACTLY as you have it in your code. Should be an easy fix with a little investigation from you. Please post back if you get stuck.

 

[exceltadpole]

Changed the "y:icontrol.xls" to "y:\icontrol.xls"
Checked the spellings.
Still I get the same error message at the same line.

Btw, it is correectly opening control.xls file and selecting the target cell range.
The target cell range has a flashing dotted line around it so I assume that the data is copied. Correct?

But then for some reason it still fails at the above error message.

 

[Tom Urtis]

Probably you do not have the workbook activated that contains the pnumSheet. This line
' select current workbook and paste the values starting at U4
suggests you have a workbook activated but it isn't evident in your code. Check again which file should be active, and asjust teh code at that point to actually activate the workbook with pnumSheet.

 

[exceltadpole]

Thank you Tom.
It was my mistake.
I added
Windows("qsys.xls").Activate
above
Sheets("pnumSheet").Select
and it is now working.

One follow up question (after the data paste is done) - it is asking me:
There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?
So I click on the NO button.

How can I make this go away from within the above code?
My target data is already pasted into my spreadsheet.
I don't need the clipboard data.

 

[Tom Urtis]

See if sticking this code at the end helps; it clears the clipboard.
If not, post back.

Application.CutCopyMode = False

 

[exceltadpole]

Thanks again Tom.
Putting - Application.CutCopyMode = False
Before - Windows("icontrol.xls").Activate

Did it.
Now it does a clean, open source xls, select range, copy range, select destination xls, paste values and close source xls.

Super.