Mapping data from various row data from columns to variable cells and sheet names

0


I have a macro that is creating sets of workbooks from multiple different sheets. For each row within my worksheet table a new workbook is created with certain sheets added to each new workbook depending on a key word in one of the columns on the same row.

I am trying to create a way to map the other row data in a table to certain cells and sheet?s depending on the key. And also create a way to make the sets of sheets variable and key?s variable.

I can do this with my current code however it?s fixed in code

I want this functionality to be variable through a user form , or even through a mapping table of sorts on my main worksheet. Rather than changing the code each time the sheet names change

As I mentioned I currently can carry out this functionality yet I want my employees to be able to determine where their data sets go and how the sheets are grouped.

The other part is I can?t have references to worksheets as the files are emailed and passed throughout the different areas of the business ***** edit**** The drop downs are determines by a FSO, basically only allowing the user to select from a standard set of templates in a specific folder.

Below is a sample of the variable data I wish to map left column is the Key for the sheet set /TYPE.Data to be mapped image 1

Below is the Proposed layout for the grouping of the sets/TYPE, this determines what sheets are to be added into each set or type. Set Grouping image 2

The output documents are variable however generally look lilke the following, and required to be in any of the cells that the users wishes. output data example

The following is a snippet of the current code that is being used

If worksheetSet = "CB" Then
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX)").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ElseIf worksheetSet = "DH" Then
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)
ThisWorkbook.Sheets("XXXX").Copy After:=targetWb.Sheets(targetWb.Sheets.Count)

Populate relevant cells in each sheet
For Each sht In targetWb.Sheets
sht.Range("C1:C2").Value = ThisWorkbook.Sheets(1).Range("AI14:AI15").Value
sht.Range("L2").Value = ThisWorkbook.Sheets(1).Range("AI16").Value
sht.Range("L3").Value = wsCxList.Cells(i, "B").Value
sht.Range("C3").Value = wsCxList.Cells(i, "D").Value56C79D74-DD77-4A88-98DA-D4168321A54B.jpeg3E09D5B5-31A6-4211-927B-628924A77CA5.jpeg

Just use worksheets codenames rather than worksheets names as a codename does not change when a worksheet is renamed …