Excel VBA Macro writing

In brief, write VBA to: copy 1 column (J) from each of ~40 files in single directory, paste into new spreadsheet ("Masterfile"), apply user-editable scoring definitions at "category" and "class" levels, consolidate into final spreadsheet

Description of excel work: 2 pieces of work:
A) Macro to:
1) Look in resident directory for all Excel files
2) For each file:
- Insert file name to Row A
- copy Column J to masterfile (where macro resides) on Worksheet “Import”

3) Once all data copied, run routine that applies “category scoring rules” generated from Worksheet
See Boolean logic explained in worksheet "Category & Class Scoring"
4) Once category scoring rules applied, run routine that applies “class scoring rules”
5) Fill adjacent columns (i.e. D for first project, with 1 for C, 2 for N, 3 for F, N/A for not applicable, N/K for not known)
6) Generate additional worksheet with Project Name in Row A, and each column corresponding to the C/N/F column originally imported, plus generated category and class values

B) Structuring Boolean logic in Excel such that it is easily (by a non-VBA user) changeable in where the thresholds lie

Possible follow-on work r.e. pivot tables and charts
Extra notes: See attached Excel file for input data structure, and boolean logic of category/class scoring