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

Popular posts from this blog

'hasOwnProperty' in javascript -

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

Command prompt result in label. Python 2.7 -