excel - Subscript out of range when referencing a worksheet in another workbook from a variable -

-

see picture: http://s12.postimg.org/ov8djtuh9/capture.jpg

context: trying activate sheet (variable: csheet) in workbook , paste data there copied data different workbook. i'm getting subscript out of range error whenever try activate directly using variable (i.e. worksheets(name).activate) or try define worksheet using variable , activate it. i've tried other coding styles, using "with worksheet" etc. , code lot longer started on because every time fix something, else goes wrong. so, sticking basics. appreciated.

sub gensumrep()  dim autosr workbook dim asrsheet worksheet dim tempwb workbook dim datawb workbook dim secname string dim oldcell string dim nsname string dim csheet worksheet  set autosr = activeworkbook set asrsheet = autosr.activesheet  = 3 10      secname = asrsheet.range("d" & a).value      if secname <> ""      workbooks.open range("b" & a).value     set tempwb = activeworkbook     'tempwb.windows(1).visible = false      autosr.activate      workbooks.open range("c" & a).value     set datawb = activeworkbook     'datawb.windows(1).visible = false      autosr.activate          'copy paste data         b = 24 29         oldcell = range("c" & b).value             if b = 24             nsname = trim(secname) & " data"             set csheet = tempwb.sheets(nsname)             else             nsname = asrsheet.range("b" & b).value             set csheet = tempwb.sheets(nsname)             end if          'copy         datawb.activate         range(oldcell).select         range(selection, selection.end(xltoright)).select         range(selection, selection.end(xldown)).select         selection.copy          'paste         tempwb.activate         csheet.select         range("a1").select         selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false         application.cutcopymode = false          b = b + 1         next b      end if  = + 1  next  end sub

you error 1 reason: the name provided not exist in collection!

there couple of reasons based on code:

  • your nsname variable contains hidden characters make different though appears correct.
  • you looking sheet in wrong workbook.

based on comments, it seems looking in wrong workbook. way check out these subscript errors iterate collection , print out names included therein.

dim sht worksheet     each sht in tempwb.sheets     debug.print sht.name next sht

in general, desirable rid of calls select , activate not relying on interface in order objects. see this post avoiding select , activate more info.

one idea applied code assign workbooks directly without activeworkbook:

set tempwb = workbooks.open(asrsheet.range("b" & a).value) set datawb = workbooks.open(asrsheet.range("c" & a).value)

instead of:

    workbooks.open range("b" & a).value     set tempwb = activeworkbook     'tempwb.windows(1).visible = false      autosr.activate      workbooks.open range("c" & a).value     set datawb = activeworkbook     'datawb.windows(1).visible = false

Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more