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

exceltadpole

Board Regular
Joined
Jan 14, 2004
Messages
108
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

Board Regular
Joined
Jan 14, 2004
Messages
108
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

Board Regular
Joined
Jan 14, 2004
Messages
108

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
See if sticking this code at the end helps; it clears the clipboard.
If not, post back.

Application.CutCopyMode = False
 

exceltadpole

Board Regular
Joined
Jan 14, 2004
Messages
108
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top