MACRO EXAMPLE BOOK.xlsx

MACRO EXAMPLE BOOK COMPLETED.xlsx

So, I have 5 worksheets in one excel file. I don’t think any of the tasks are particularly challenging, but it’s just overwhelming with all the sheets and all the steps. Especially for a VBA novice like myself! I’m attaching the file for reference. The second file is the completed product (minus formatting for file size) based on Part 8 and Test 5. Please don’t be afraid to ask for clarifications on anything! I know it took me a bit to wrap my head around it!

First there will be two prompts. One will ask the user “Enter Name here as shown in sheet 4” and he/she will enter for example “Part 8”. The second prompt will ask “Please enter the name of your test as shown in sheet 3” and the user will type something like “Test 5”.

In response to the first prompt, the macro will do two things. It will look at ‘sheet 4’ row 2 to find the cell corresponding to the input text. That text will be displayed in ‘sheet 1’ merged cell set A1-E1. Additionally, the macro will find all non-blank/non-zero cells in the column indicated in ‘sheet 4’. Based on those non-blank/non-zero cells, the macro will take the corresponding cells from column B of ‘sheet 4’ and copy them to column B of ‘sheet 2’.

Now the macro will look to ‘sheet 4’ column B and create a unique list of all items listed and put that unique list in ‘sheet 2’ column A.

In response to the second prompt, the macro will look to ‘sheet 3’. It will look to row 1 for the text corresponding to the correct cell (i.e. Test 4). The column pertaining to this cell will be searched for all non-blank cells. The cells in column D, E, and F which correspond to the rows of the non-blank cells will be copied into ‘sheet 1’ columns A, B, and C.

You may notice that not all of the items in sheet 3 column D are 100% numeric. In this instance, the X’s represent wildcards. ‘Sheet 5’ contains a master list of all potential items covered by the X’s. Instead of directly copying the cells containing ‘XX’, I would like to reference sheet 5 and instead copy over all cells covered by the wildcard designation (for example, if the item is 1204XX would pull over cells 120400, 120401, 120402, and 120403). The ‘Title’ and ‘Notes’ cells corresponding to the wildcard items in ‘sheet 3’ will be the same for each item covered by the wildcard.

It would be great to include an error message with the prompts in case of an invalid entry.

Thank you in advance for your help