VBA InputBox Issue

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.

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

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.

Your initial question is constantly expanding to include more requirements.

I don't really understand all of your last post. I am happy to continue assisting you but I need to be able to see the entire picture from start to end. ie. I need a copy of the initial data (if I am understanding correctly it is a csv file). I also need a dummy Output workbook with the data displayed as required before saving. If you have sensitive data in the workbook then replace with dummy data.

In your previous post I can't reconcile the following with the screen shot.

Column G: No, but excluded in the RU under Clause III Exemption 6(a), Clause III Exemption 6(b), Clause III Exemption 6(c), Clause III Exemption 7(a), Clause III Exemption 7(c)-IV

What I do is introduce line breaks from the word, Clause (everything in one cell) and remove other text.

e.g Column G (after clean up)

Clause III Exemption 6(a),

Clause III Exemption 6(b),

Clause III Exemption 6(c),

Clause III Exemption 7(a),

and Clause III Exemption 7(c)-I

After inserting the line breaks, line 5 is prefixed with "and" and the end of line 5 is missing the last character ("V"). Is the "and" really required?

Then your screen shot is changed from above. ("and" omitted from line 5 and line 6 introduced and prefixed with "and". Line 6 text not anywhere in the initial Column G value).

I think that I understand that you want to loop through the numbers in column A and produce a workbook for each unique number. It is easy enough to create a unique list of the numbers from column A and can create a loop and save and close each created workbook as per you requirement of workbook name (respective unique number in Column A and description of the number Column B as the file name.)

If you can upload the workbooks to OneDrive then I will take a look at the entire project. I am sure you have heard the saying "A picture is worth a thousand words". I think that a copy of a workbook is worth much more than that.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

Sharing links to business OneDrive often does not work because the business has applied security measures that prevent this. Some people take a copy of the workbook home and upload from their private OneDrive.

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link. https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file name in OneDrive.
9. Select Share.
10. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
11. Click Copy button.
12. Change back to this forum and click the "Insert Hyperlink" icon at top of the posting editor (Icon looks like chain links).
13. Right click in the Web address field and right click and paste (or just Ctrl V to paste).
14. Click "Insert" Button.