VBA InputBox Issue

After spending last few days creating the data set and troubleshooting errors. At last I found that the macro works well on sample data set.

But with the actual data set it works only if the column order is retained as in the wsSource sample data set workbook. If not the categories worksheet is all N/A even though there is data in the wsSource workbook.  

I though it looks for data based on the column header name but when the column order changes output sheet works well but not the categories sheet.

Also if the Description has / in it (e.g Device/340/540 or UTP/FX switch) it runs into a error file path not found. I replaced all / with space to avoid the error. 

I hope you will be here to support if I face some issue with the macro in future. Because the actual dataset is huge as I run the macro further. I might encounter some issue like above e.g going forward. 

In category 3 can we wrap the text as it is extending beyond the cell boundary. Let me know at which line the change is required so that I will add that line of code to my already adopted workbook.

Have you also changed any column positions in the output workbook?

If you upload an example of the source data then I will have a look at it and try to align the source columns with the output columns. Don't need all rows of the source data; just enough rows to test.

Please include a list of the columns in the source data against a list of the matching columns in the destination. Just create the column lists in a worksheet so that one row has the source column names and the row under has the destination columns to which the data goes. Leave out any source data columns that are not copied.

No. The template has no change in the order in both the Output and Categories. I follow the same order.

In the categories sheet irrespective of the heading names it populates the categories sheet based on the names defined in the code that is good. Those names are found in the ws_Source_2 workbook.

Issue is with wsSource_2 column order. In wsSource_2 workbook when I create the data set every week with updated data. The original source workbook has 50+ columns. I transform it through power query. I select the required columns and ignore the rest. I do joins to do lookup the Sub Name and Catergories from another workbook and Column W data from CSV is also lookup'd into the wsSource_2 workbook. 

When I do this exercise every week the column order can get changed slight or more in wsSource_2. 

Since you have the wsSource_2 workbook with you I will just mention the column names here. 

If I change the order of the below columns could you check from your side does it still populate the Categories worksheet? Especially Sub Name, CQP, Category, columns try to move them to different place within A to W.

Unique number

Descr

Half Number

VC

Name

SUB 1

SUB 2

SUB 3

SUB 4

SUB 5

SUB 6

SUB 7

SUB 8

Evidence details

Sub Findings

S characteristics

(if applicable)

Sub Name

CQP

Category

Code

The below heading "S characteristics (if applicable)" has line break in the heading. But I did not face any issue when the code has the heading name as "S characteristics" It still recognizes the column. 

S characteristics line break

(if applicable)

The above are the column headings (20 columns) for both Output sheet and Categories. The sample data set has 23, extra 3 columns will have data for Coversheet B13, B14, B15 cells. The data is not available for them right now.

Retaining the column order is not much of a problem now that I know it.

The variant for Scenario 2 link. 90% of the code stay same as you can see from the template. Only addition is the Unique number column in both the Output and Categories sheets. Some changes to the Coversheet.

The Unique Number list has the numbers, name and type to populate the templates. This can come from a separate excel or I can paste it directly. Based on the type the template gets populated.

In categories sheet the Name column gets its name from Unique list sheet instead of wsSource name column.

File Name saved as follows MCR-GEN-Unique Number-001_Name

Unique Number and Name from Unique list sheet.

Word GEN and 001 stays same for all the files.

Let me know if it not clear.