I must rely on those more fluent in VBA and array manipulation than moi.
For the life of me I'm stuck.

I am placing into an array columns A, B, and D, NOT C.
Appears this must be done with a union - if not let me know.
Yes, have Dim myData as Variant

Set facilityID = Wks.Range("A:B")
Set triChemical = Wks.Range("D:D")

myData = Union(facilityID, triChemical)

In column C is a year. I use an inputbox to tell Excel which year I want data for, e.g., 2014.

I use an If statement to test the year, i.e., variable name YeatToInput

'Test the to see which year to extract data
If bigArray(i, 3) = YearToInput Then
Worksheets(NewWorkSheetName).Range("A1:C3").Resize(UBound(myData, 2)).Value = myData
Worksheets(NewWorkSheetName).Columns("A:C").AutoFit

A new worksheet is generated and some data is pasted to the worksheet, but the last column ends up #NA, I have no clue why?

Output example:

Col A Col B Col C

88330HLLMNHWY70 US DOD USAF HOLLOMAN AFB #N/A
88330HLLMNHWY70 US DOD USAF HOLLOMAN AFB #N/A

Also, there should in fact be 3 rows of data not 2.

Suggestions on correct way to output would be appreciated.

Help an ole Vet on Veterans Day - smile.
Thanks,
Mort