VBA InputBox Issue

@ 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

Merge same cells as one from A:E for Category_3

Please confirm if this screen shot what you mean. If not correct then please provide a screen shot of what is required. I will look into the other problems.

Looks good! Scenario 2 is almost done. Thank you very much!

Can continue with the fine tuning before I test it on the actual data set. 

Merge same cells as one from A:E for Category_3

Middle and left align all the cells in the Categories sheet (can be set in the template so that it replicate to all workbooks)

Switch to Page layout from normal. (It replicates what I set in the template so no additional code required)

Scientific number converted to number format. (maybe we can format the template cells to number format or better format the numbers after populating them)

Wrap Column G

Go to A1 cell  instead of leaving it as whole selection. I wanted to mention this in Scenario 1 as well.

merge cells and N/A if no data exist as shown for Category_4 in the template

Files at the following link so test and see if doing what you require.  https://1drv.ms/u/s!ArAXPS2RpafCtG9sIwwsX4d8ETOZ?e=Mef9Yl

In addition, to Unique number sheet the Categories worksheet should also be populated from the wsSource workbook for some unique numbers. It is like extending the earlier code further for the Categories worksheet. Workbook naming and all stays the same.

Like I said out of 3000 unique numbers a small portion about 700-800 numbers have additional data for Categories_1,  Categories_2  and Categories_3. That data goes in to the Categories worksheet based on the respective filters.

Some unique numbers might have data for any one or two Categories and no data for others then I include an N/A as shown in Categories_4.

So in Scenario 2 I fill two sheets instead of just one for a range of unique numbers at a time and save it with Unique number and description. 

There is no need to insert rows for each of the categories. I am thinking that for Category 1 we simply copy the "Category 1" column headers and the data goes under it for whatever number of rows are required.

I guess that would be much convenient to paste and format it afterwards accordingly rather than trying to fit into the template layout.