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