VBA Set Named Range

[DRWonoski]

In a workbook I have titles to a table in Row 2. I want to set a named column based on those titles as publicly accessible throughout all of the code in the workbook. I'm currently getting a Compile Error: Type Mismatch with .Match being highlighted. Thoughts?

 Public ColPWSN As Range

Set ColPWSN = WorksheetFunction.Match("PW SN", ActiveWorkbook.ActiveSheet.Range("2:2"), 0)

 

[DRWonoski]

Here's the big picture - I have a few different stores that all track orders using an excel spreadsheet similar to what you see below. Each one is named "Town Order Tracker". I also have a "master" file that feeds all of the columns you see highlighted in tan. "Pull2" comes from Master!Sheet1 and "Pull1" comes from Master!Sheet2,3,4,5, or 6 (each store has their own sheet. All information is found on Pull1 first, and then when entered into our ERP system it gets moved to pull2 (AKA anything that shows Pull2 currently has a formula to state "TBD" until a match is found on Pull2.

I want an easier way to add new lines and mark orders as shipped. When orders ship, I currently copy everything and paste as values so the index(match isn't lost when orders fall off of Pull1. I then change a column to say "closed" instead of open.

To add a new order, I want an input box to appear asking for the PO number. I then want excel to look in "Pull2" to determine whether it is "Cat, Dog, or Elephant" and place a new formatted line below the last of that type. All tan shaded boxes should automatically pull from Pull1 or Pull2. If the PO isn't found, I want the employee to be able to manually enter whatever Cat or Dog and then manually add any Pull 1 Information.

Before State: https://imgur.com/eKLjNil
After Adding New Dog: https://imgur.com/yTTrKlY
After "Shipping" Dog: https://imgur.com/Klsvmv0

 

[Eric W]

I'll be away a few days. I'll check back when I can to see if this is resolved.