VBA InputBox Issue

Take your time.

I also need time to understand the code because it is slightly different from Scenario_1 code.

If I am unable to understand the logic I will let you know.

@ExcelFunTension,

No problems with the changes you have requested. I will implement them all. It is nearing my bedtime tonight so it will be tomorrow before I continue with it.

If not maybe we needto sort it in such a way everything is grouped together*.*Yes! I think that is a wise suggestion. I have the code written to perform the merging and I realized that it is necessary that the like rows be adjacent and I intended to ask you if it is OK to sort the data so you have answered my question before I asked it.

 It is my intention to continue working with you until you are satisfied with the end result. I rather like a challenge with a bit of complexity instead of the mundane simple question and answer.

e)Columns O, P, and Q in the Output sheetwill always have the same values

Location="New York"

Date= current date

Status= "C"

You might don't need to have it in the code, the template can handle it (but it is up to you guys)

I though of the same initially but O, P and Q needs to be dynamic have to adjust with the number of rows populated from the wsSource or as you were suggesting delete the extra rows data from the columns O, P and Q from the template as per the last row. 

Category_1 to Category_4 needs to be in a single sheet appended after one after other with a row gap. I am not allowed to change the template format.

I did not go through your code I saved your file.

I still have to go through OssieMac code as well. 

I also saw ByVal being used in Ossiemac code. I do want to use arrays and passing arguments in the code where applicable.

Once I have the final code. I will spend time to understand it and clear my questions.

Exactly, if the row values are same from A:E just merge as one. This is applicable for Category_3.

It is very much like the below code but I am worried what if same values are not next to each other. A2 =A1 logic works only if same values are next to each other only then we can merge cells. If not maybe we need to sort it in such a way everything is grouped together. In my case they are probably place next to each other.

        For Each rng In Selection

        If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then

        Range(rng, rng.Offset(1, 0)).Merge

        End If

        Next

While wrapping the text in column G. It is only applicable for Category_1 instead of the whole column in the sheet.

Category_2 doesn't need wrap text.

@ OssieMac

HAT OFF!!!!!!!   to you mate, what amazing work you have done,

I wanted to join you earlier but I really couldn't follow the thread until your last post with the link to the 2 files.

Allow me a humble suggestion since you will continue providing your assistance to ExcelFunTension

Rather than:

• .copy/paste,
• format the tables,
• finding the last row multiple times with the loop creating the files

Try having an Output Template file where you will only

• paste the data as a value
• and then save with the already planned format

The file in the link below contains the following

1. Sheet tabs distributed as in the picture below

2. I choose to separate the "Categories" sheet into their four categories (I respect the OP's choice to not do it like that, but considering the early stage of the project this approach might be good to be considered)

This approach will save you time and coding.

a) No need to find the last row

b) You'll always paste in the same range

c) Tables were converted into real Excel Tables, allowing as you mentioned in the code, to use the Tables ListObjects properties in lieu of Column/last row numbers or variables.

With a piece of code like

With wbOutput.Worksheets("Category_1").ListObjects("Category1Tb").DataBodyRange(1, 1)

        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End With

d) You might don't need the cell formatting code part. Since tables dynamically adapt/adjust their formating after pasting the filtered data

e) Columns O, P, and Q in the Output sheet will always have the same values

Location="New York"

Date= current date

Status= "C"

You might don't need to have it in the code, the template can handle it (but it is up to you guys)

f) Similar method for the "Coversheet" tab but, using Named ranges.

Description =Coversheet!$B$4

Document_number =Coversheet!$B$1

Document_owner =Coversheet!$B$12

Evaluation_date =Coversheet!$B$5

ME =Coversheet!$B$15

Project =Coversheet!$B$13

RE =Coversheet!$B$14

Record_vr =Coversheet!$B$7

Revision =Coversheet!$B$2

Unique_number =Coversheet!$B$3

The file in the link is the New_Output_Template

https://we.tl/t-LmYUwPtmxS

Do let me know how further I can help you

Best Regards

Jeovany