Oh doh! I should have remembered the query reaching into Excel since I gave that as an answer in another thread some time ago.
This does mean need to know the bottom-right cell reference and query will have to be modified each time if more than 1000 rows.
However, UNION appears to have 1 too many rows and advise converting the MoYr text to an actual date value.
SELECT Data.F1 AS ProdNum, Data.F2 AS Qty, Header.F2 AS MoYr, DateAdd("m",1,CDate(Left(Header.F2,3) & "/" & 1 & "/" & Right(Header.F2,2)))-1 AS RptDate FROM qryXL
UNION SELECT Data.F1, Data.F3, Header.F3, DateAdd("m",1,CDate(Left(Header.F3,3) & "/" & 1 & "/" & Right(Header.F3,2)))-1 FROM qryXL
UNION SELECT Data.F1, Data.F4, Header.F4, DateAdd("m",1,CDate(Left(Header.F4,3) & "/" & 1 & "/" & Right(Header.F4,2)))-1 FROM qryXL
UNION SELECT Data.F1, Data.F5, Header.F5, DateAdd("m",1,CDate(Left(Header.F5,3) & "/" & 1 & "/" & Right(Header.F5,2)))-1 FROM qryXL
ORDER BY ProdNum, RptDate;