VBA InputBox Issue

Thank you for extending the support.

I wanted to upload the empty template but again my original post was answered. So I though the additional requirement might be asking too much. As you are comfortable to go through the template. I have uploaded the file in the link.

It can be confusing. I will put it briefly of what I am trying to accomplish.

There is a template with five sheets. Sheet1 Coversheet, Sheet2 (code is already available in your post), Sheet3 (the image I shared), Sheet3 is more specific. Sheet4 and 5 are static. For sheet3, I find the unique number in the source file and then apply Category 1 filter get the data if data exists. remove Category 1 apply Category 2 get data if data exists same with Category 3 filter.

For one unique number in sheet3. I apply three to four filters and pull the data into the template.

Filters:

unique number Category 1

clear filters

unique number Category 2

clear filters

unique number Category 3

clear filters

unique number Category 4 (If necessary)

clear filters

I have two scenarios here:

Scenario 1

For a large chunk of unique numbers there is no data available for Sheet3. So the code we have already in place can be used on those unique numbers at a time to populate the templates with each unique number and save them in a folder.

Scenario 2

For about 30% of the unique numbers there will be data available for Sheet3.

I plan to extract those unique numbers from the source workbook. Get the data for Column G, from CSV file using the unique number. Then use that file as wsSource to populate sheet3.

Therefore, apart from filling in sheet2. I also need to fill in the sheet3 based on the category filters for those 30% unique numbers.

First challenge is to populate the CSV data into the source workbook with line breaks. Once I have everything in place. I start to populate the template with a range of numbers instead of inputting one number at a time in the input box and saving it. (I will make sure that the data exist for those input numbers at least in one of the categories for Sheet3).

Like I said before:

I have a source workbook and one column into that source workbook comes form a CSV downloaded from the portal. That information from the CSV goes into the template's Column G: 

The data in CSV column Q for each unique numbers will be as shown in the screenshot below. The Column Q CSV entries go into template Column G via wsSource workbook.

I am not able to share the workbook because I have not yet created wsSource with CSV Column Q data. It is hard to create line breaks for 800 unique numbers although a small percentage of rows does not require line breaks..

Original CSV

![Image](https://learn-attachment.microsoft.com/api/attachments/260eb9f4-6d00-44c5-ae29-44133b6bf5b1?platform=QnA

CSV after Line breaks

After I copy that data from the portal CSV. I need to clean it up and add line breaks. The picture I shared before and the text did not match exactly because I took the example text directly from the portal and template has info relevant to other unique number. Roman numbers also exist in the CSV. e.g 7(c)-V

"and" is already there in the CSV no need to add.

In summary, I have to prepare wsSource workbook with additional row from CSV Column Q to populate the Sheet3. Thereafter, it is just as you written the code before for Sheet2 wsSource to wsOutput with filters.

It is all too complex to be sure that I am interpreting correctly. I was hoping to get workbooks including the data so I could properly test.

The following example code will convert the string data in column Q by removing the leading string and then adding Line Feeds where required. You will need to integrate with your existing code before copying to the Output sheet.

If the leading words in the cells changes with different records then it will not work and it might be necessary to loop through the data and remove any text before the first occurrence of "Clause"

The Find and Replace method is much faster than looping through the data and converting each cell.

Code edited since initial posting to try to align to workbook and worksheet names used in previous examples.

Sub Macro1()

'This code run on source worksheet

Dim wbSource As Workbook

Dim wsFull As Worksheet

Dim rngColQ As Range

Set wbSource = Workbooks("ML.xlsx")

Set wsFull = wbSource.Worksheets("Full-View")

With wsFull

    ***'Following set for column Q of source data so edit Q if not correct.***

    Set rngColQ = .Range(.Cells(2, "Q"), .Cells(.Rows.Count, "Q").End(xlUp))

End With

With rngColQ

    ***'Delete the leading words no longer required***

    ***'(Code actually replaces the words with zero length string)***

    .Replace What:="No, but excluded in the RU under ", \_

            Replacement:="", \_

            LookAt:=xlPart, \_

            SearchOrder:=xlByRows, \_

            MatchCase:=False, \_

            SearchFormat:=False, \_

            ReplaceFormat:=False

     ***'Replace the comma with comma plus a Line feed***

    .Replace What:=", ", \_

            Replacement:="," & Chr(10), \_

            LookAt:=xlPart, \_

            SearchOrder:=xlByRows, \_

            MatchCase:=False, \_

            SearchFormat:=False, \_

            ReplaceFormat:=False

     ***'Replace " and" with a Line Feed plus "and"***

     ***'(Appears that there is no comma for last part***

     ***'so needs to be handled on the "and")***

    .Replace What:=" and", \_

            Replacement:=Chr(10) & "and", \_

            LookAt:=xlPart, \_

            SearchOrder:=xlByRows, \_

            MatchCase:=False, \_

            SearchFormat:=False, \_

            ReplaceFormat:=False

    .WrapText = True    ***'Turn on Wrap Text***

End With

End Sub

Great! the code works.

I understand it is a bit complicated but if I start getting into the detail you get more questions.

I somehow created a very simple data set. However, original data is huge. There are many variables.

I created two folders Scenario 1 and Scenario 2

Scenario 1

Is straight forward you have unique numbers in wsSource file we input all the numbers by selecting the range in the input box. Macro starts to populate sheet2 and some minor details in Coversheet they come from the same wsSource columns. No need to populate Sheet3 in this case. Then we save them in a folder with Unique number and its Description as the file name.

Scenario 2

Get the Column G from CSV to wsSource (not sure if you wish to introduce line breaks at this stage).

Once wsSource has CSV data in the Column W.

wsSource is the source workbook.

We populate the Sheet2, unique number and description details in Coversheet,

Sheet3 based on Category filter in Column V.

Then save the workbooks into a folder with Unique number and its Description as the file name.

No good uploading rar files for me. I need zipped files. See Point 3 in my guidelines for uploading to OneDrive.