excel vba - Subscript out of range error VBA when copying multiple sheets -


i have vba function using copy of list of sheets active workbook , save it. getting subscript out of range error. appreciated?

private sub exporttoexcel(excelfilename) excelfilename = 'file in local' dim mysheetlist() string dim ws worksheets dim wb workbook msgbox excelfilename 'dim sourcewb workbook 'dim destwb workbook ' 'set sourcewb = activeworkbook 'sourcewb.sheets.copy ' 'set wb = workbooks(excelfilename)     redim mysheetlist(0 (thisworkbook.sheets.count) - 1)      dim integer       = 0        each ws in activeworkbook.worksheets           mysheetlist(a) = ws.name           = + 1        next  dim fileobj set fileobj = createobject("scripting.filesystemobject") if fileobj.fileexists(excelfilename)     fileobj.deletefile excelfilename end if  '     'actually save      worksheets(mysheetlist).copy '<<<<<<< run-time error 9 raised here      activeworkbook.saveas filename:=excelfilename      'msgbox excelfilename       'thisworkbook.saveas filename:=excelfilename, fileformat:=xlxmlspreadsheet      application.wait (now + timevalue("0:00:15"))      wb.close  end sub

i think have passed sheet list of names of sheets, i.e., "sheet1","sheet2",etc. however, when passing array function sheets(array(mysheetlist)) must used.

'worksheets(mysheetlist).copy sheets(array(mysheetlist)).copy

Comments

Post a Comment

Popular posts from this blog

'hasOwnProperty' in javascript -

i use obj.hasownproperty judge whether object has property, when replaced obj[prop] !== undefined , not normal implementation, ask, why behind method can not use it? object.hasownproperty(prop); object[prop] !== undefined; obj[prop] !== undefined wrong 2 reasons: you can explicitly set property undefined , obj[prop] = undefined; . obj.hasownproperty(prop) return true in case. obj[prop] follow prototype chain, return property that's inherited. obj.hasownproperty(prop) returns true if property exists directly in object, returns false inherited properties.
Read more

python - ValueError: No axis named 1 for object type <class 'pandas.core.series.Series'> -

i´m new programming. i´m trying use scipy minimize, had several issues , gotten through of them. right code, i'm not understanding why i´m getting error. par_opt = so.minimize(fun=fun_obj, x0=par_ini, method='nelder-mead', args=[series_pt_cal, dt, series_caudal_cal]) not enough info given op, somewhere in code it's specified operate data frame column (axis=1) on object pandas series. if code typically works occasional gives errors, check degenerative cases data frame may have 1 row. pandas has nasty habit of guessing want -- may decide reduce 1-row data frame series (e.g., apply() function; can disable using reduce=false in there). add line of code check object isinstance(df, pd.dataframe) or else convert offending pandas series data frame, s.to_frame().t problems had deal with.
Read more

Command prompt result in label. Python 2.7 -

i want put result(output) of shell command label. command works, label shows "0", however, in command prompt, result shown correctly, need showed in label. doing ubuntu. myg1 = button(root, text="rodyti informacija", command=lambda: gauti()) myg1.pack(side=bottom) def gauti(): imti = tekstas.get("1.0", "end-1c") info = subprocess.call("id '{imti}' ".format(imti=imti), shell=true) w = label(root, text= info) w.pack(side = bottom) subprocess.call() returns exit code of process created (in case 0 success). if want text output of process, should instead call subprocess.check_output() .
Read more