VBA InputBox Issue

Yes, Ideally there won't be any blanks for Category_1 for this example there are blanks but no need to include blanks filter in the code.

For  Category_2 Column T and Column U has the required data.

For  Category_3 just the Column T

For Category_4 nothing to lookup it stays N/A

For Category_2 and Category_3 there are no blanks same is the case with Category_1
I must have missed to fill in all the rows. 

Category_3 Columns A to E rows are merged if the rows values are the same.

I attempted to write the code after setting the filter based on the wsSource rows. I need to create empty rows to paste the data. I might be doing it in a lengthy manner. I better leave it to you.

The below code is incomplete, code has yet to determine the numbers of rows based on COUNTA that was used earlier in the code.

Sub add_rows()

Dim wb As Workbook

Dim ws As Worksheet

Dim ws3 As Worksheet

Dim LastRow As Long

Set wb = ActiveWorkbook

Set ws = wb.Sheets(4)

Set ws3 = wb.Sheets(3)

LastRow = wb.Sheets(3).Range("A" & Rows.Count).End(xlUp).Row - 3

    For r = 7 To LastRow

    ws.Cells(r, 1).EntireRow.Insert

    Next r

End Sub

I don't understand how to set the criteria to extract the required data for the Categories.

For Category 1 if I set the Category filter to to Category_1 and the Code to no blanks then I can match the example you have provided for Category 1 but I am not sure that is what is required.

For Category 2 and Category 3 I have no idea what criteria I am required to use to get anything like the data in your examples.

Please set out step by step what criteria I need to use to extract the data for each criteria report.

I have attached the folder for Scenario 2 link

Extra space appeared when I copied some unique numbers from wsSource and pasted into a new workbook and converted into a table. Then for one column (Name column) I had that issue rest are fine.

Scenario 2 link

The below code could be reduced to one line

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")) = Date

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")).NumberFormat = "dd mmm, yyyy"

The Format function returns a String (Text) that can no longer be used as a date without conversion back to a date. I very much doubt that the one line of code to set the Number Format is going to make any  measurable difference to the time taken to run the code. In fact, if it is actually measurable, it would not surprise me if using the Format function takes longer due to the work involved in making the conversion to a string.

Also I noticed when there is a trailing space in wsSource >> Table >> Column Name it throws error code 9.

I had previously indicated that I had a problem with one of the headers in a previous post as per the following paragraph.

Note where I have copied the columns that I am now using the table column name in lieu of the column number (It makes it self documenting and you don't have to count up the columns. If any fail then probably due to a leading or trailing space on the column header in the table (I actually had one of these)

The table headers need to be corrected before using them because they must match exactly to the column name used in the code.

It is not possible to remove the additional spaces in the header from within the line of code. (It must be done before using the specific line of code).

Another problem is that they are not necessarily spaces, particularly if the data has been downloaded from elsewhere and they can be some other non printable character that appears like a space and the functions Trim and/or Clean do not remove them.

If you would like to provide me with a full copy of all of the headers in the table then I will provide some code that should ensure the headers are correct before the remaining code runs. 

To provide the list of column headers.

1. Add a worksheet to the workbook containing the table.
2. Select and copy the table headers.
3. Select the first cell in the new worksheet and PasteSpecial -> Values -> Transpose -> OK   (Values and Transpose both selected in the same dialog)
4. You should now have a vertical list of the table headers.
5. Copy the list and paste into your reply on the forum.
6. You can delete the extra worksheet.