i have ms excel workbook containing many worksheets.
users enter data in 1 of worksheets , click button runs vba code. based on conditions derived user entered data, need create new workbook containing copies of of worksheets.
the code iterates (loop) through worksheets in source workbook , add names of relevant worksheets string...
dim worksheetstocopy string worksheetstocopy = worksheetstocopy & """" & "admin tab" & """" & ", " after loop completed inspect variable debug.print worksheetstocopy gives me following output in immediate window: "admin tab", "home tab", "dashboard",
then strip-out last ","
worksheetstocopy = mid(worksheetstocopy, 1, instrrev(worksheetstocopy, ",") - 1) then, when try copy worksheets contained in variable worksheetstocopy following line of code, runtime error 9 - subscript out of range:
sheets(array(worksheetstocopy)).copy however, when run same instruction manually enter tabs names in quotes , commas below, works fine:
sheets(array("admin tab", "home tab", "dashboard")).copy why runtime error 9 - "subscript out of range" error first statement.
what doing wrong or missing? appreciated...
here solution error described in topic.
the worksheet in ms excel code executes when user clicks "create new workbook" button:
option explicit sub buttoncreateworkbook_click() ' ' buttoncreateclientworkbookk_click macro ' macro creates new workbook containing specific worksheets based on selection ' set in "control" worksheet of workbook ' dim strworksheetstocopy string dim wbsourceworkbook workbook dim strcontrolsheet string dim irowstart integer dim irowend integer dim strworksheetcolumn string dim strswitchcolumn string dim irow integer dim strsheetarray() string set wbsourceworkbook = application.activeworkbook strcontrolsheet = "control" wbsourceworkbook.sheets(strcontrolsheet).activate strworksheetcolumn = "b" 'this variable indicates irow contains worksheet names irowstart = 2 'this value must set irow number first worksheet name occurs in column 'this value must set irow number last worksheet name occurs in column b irowend = cells(rows.count, 2).end(xlup).row strswitchcolumn = "c" 'this column must set value of "on/off switch" column on "control_showhide" worksheet. 'this section of code controls worksheets must copied , add name of worksheet string irow = irowstart irowend 'check if worksheet must copied if wbsourceworkbook.sheets(strcontrolsheet).range(strswitchcolumn & irow).value > 0 strworksheetstocopy = strworksheetstocopy & wbsourceworkbook.sheets(strcontrolsheet).range(strworksheetcolumn & irow).value & "," end if next irow debug.print strworksheetstocopy 'remove last , string strworksheetstocopy = mid(strworksheetstocopy, 1, instrrev(strworksheetstocopy, ",") - 1) 'transpose string string array strsheetarray = split(strworksheetstocopy, ",") 'create new workbook relevant sheets wbsourceworkbook.sheets(strsheetarray).copy end sub 