Runtime error 9 "subscript out of range"

**Frustratedemployee** · 08-29-2013, 03:56 PM

Hi I am trying to automate a process from which i copy a sheet ("NA") from a workbook (released daily with date as its name) and paste it into an existing workbook ("Overflow Log.xlsx"). Here is the code I have so far.

Please Login or Register  to view this content.

The code opens the existing file (overflow log) without any issues but fails at "ThisWorkbook.Sheets("NA").Copy After:=wbOpen.Sheets("Graph")" giving me Runtime error 9 "subscript out of range". Note: the copied sheet needs to go after or before a sheet named "Graph" in the Overflow Log workbook.

any help will be appreciated. Thanks

**Solus Rankin** · 08-29-2013, 04:53 PM

Please Login or Register  to view this content.

**watersev** · 08-29-2013, 04:56 PM

hi Frustratedemployee, welcome to Excelforum, this error means that either you do not have sheet "Graph" in the destination workbook (or it is named differently) or there is no sheet "NA" (or it is named differently)

**Solus Rankin** · 08-29-2013, 05:01 PM

@ watersev

Just out of curiosity, won't opening the wb make it active? After its active will thisworkbook behave as normal?

**Frustratedemployee** · 08-29-2013, 05:06 PM

Thanks for your response gentleman, but its still a no go. I have read in some other threads, where it was suggested that the sheet name I am using in my code may not exist or may have a space before or after., so I had already made sure of that. See below
Capture1.JPG Capture2.JPG.

Maybe I am missing something else with the naming of the sheets? Also, with XeRo Solus's code the error changed to THIS.JPG

**Solus Rankin** · 08-29-2013, 05:21 PM

chart type? Is this included with other code?

Can you upload your workbooks?

**watersev** · 08-29-2013, 05:24 PM

@Xero Solus
yes opening the workbook makes it active but it does not become thisworkbook

@Frustratedemployee

Try this if you will get any error and on what line

Please Login or Register  to view this content.

**Frustratedemployee** · 08-29-2013, 05:44 PM

I have uploaded both workbooks.

@ watersev...your code gave the same error on line
Set main_sh = ThisWorkbook.Sheets("NA")

Thanks for your help.

**watersev** · 08-29-2013, 05:56 PM

What is the error?

If to amend file name to Overflow Log2.xlsx in your code everything works as expected on the posted files

**watersev** · 08-29-2013, 06:05 PM

try this instead:

Please Login or Register  to view this content.

**watersev** · 08-29-2013, 06:17 PM

Is your code residing in a personal macro workbook? Then it should be (amend file name if required):

Please Login or Register  to view this content.

**Frustratedemployee** · 08-30-2013, 06:48 PM

The code worked flawlessly. Thank you Gentleman, for your help. my code does reside in Personal.xlsb

Runtime error 9 "subscript out of range"

LinkBack

Thread Tools

Rate This Thread

Display

Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Re: Runtime error 9 "subscript out of range"

Thread Information

Users Browsing this Thread

Similar Threads

Runtime error 9 "subscript out of range"

[SOLVED] Runtime error 9 "subscript out of range"

Error msgs: "Object varible or with block variable not set"; "subscript out of range"

[SOLVED] "Subscript out of range" error for: Workbooks("Test1.xls").Save

[SOLVED] FileCopy Command Giving "Subscript Out of Range" Error Message

Bookmarks

Bookmarks

Posting Permissions