Split a single workbook into multiple workbooks containing multiple worksheets using Excel VBA -

i have workbook single worksheet given below.

i want split many workbooks containing many worksheets according values in it. want make 'n' number of workbooks according 'n' unique values of column 1 in picture. , want make 'm' worksheets according 'm' unique values of column 2 in picture.

each worksheet contains values in picture. want make chart 3 series. have make data table in picture columns 'levels', 'chart_vlaue_1', 'chart_vlaue_2', 'chart_vlaue_3' in each worksheet. want generate charts in each of worksheet. please me create sample chart. work on it. please me.

the following code parse data in first 2 columns create workbooks each unique cell value first column , sheet each unique cell value second column. adds charts of type xlcolumnclustered , saves , closes new books. source data can un-sorted.

important: change constants targetpath and/or databookname, datasheetname according conditions.

option explicit  ' --------------------------------------------------------------------------------------- ' results saved 'targetpath' path. path must changed according pc ' change path: private const targetpath string = "c:\temp\abdul_shiyas\results\" ' ---------------------------------------------------------------------------------------  ' --------------------------------------------------------------------------------------- ' expected data contain in sheet named "data" in wokbook name "data.xlsx" ' names can changed according wokbook data. private const databookname string = "data.xlsx" private const datasheetname string = "data" ' ---------------------------------------------------------------------------------------  private sourcebook workbook private sht worksheet private book workbook private books collection private header range private data range private criteria range private criteriarow range private bookname string private sheetname string private newchart shape  public sub parsetoworkbooks()      ' important:     ' data expected begin in cell "a1" , should not contain blank rows or blank columns     set sourcebook = workbooks(databookname)     set data = sourcebook.worksheets(datasheetname).range("a1").currentregion     set header = data.rows(1)     set data = data.offset(1, 0).resize(data.rows.count - 1, data.columns.count)     set criteria = data.resize(data.rows.count, 2)     set header = header.offset(0, criteria.columns.count).resize(1, header.columns.count - criteria.columns.count)     set books = new collection      each criteriarow in criteria.rows         bookname = trim(criteriarow.cells(1))         sheetname = trim(criteriarow.cells(2))          ' book first         set book = nothing         on error resume next         set book = books(bookname)         on error goto 0          if book nothing             set book = workbooks.add             application.displayalerts = false             book.saveas filename:=targetpath & bookname             application.displayalerts = true             books.add book, bookname         end if          ' sheet         set sht = nothing         on error resume next         set sht = book.worksheets(sheetname)         on error goto 0          if sht nothing             set sht = book.worksheets.add             sht.name = sheetname             header.copy destination:=sht.range("a1")         end if          ' paste data sheet         criteriarow.cells(2).offset(0, 1).resize(1, data.columns.count - criteria.columns.count).copy _             destination:=sht.cells(sht.rows.count, 1).end(xlup).offset(1, 0)      next criteriarow      ' , chart, save , close each new book     each book in books         each sht in book.worksheets             if sht.range("a1").value <> ""                 set newchart = sht.shapes.addchart                 newchart.chart.setsourcedata source:=sht.range("a1").currentregion                 newchart.chart.charttype = xlcolumnclustered             end if         next sht          book.close true     next book end sub