Copying and pasting to another sheet with loops

I need to write a macro that loops through all my sheets and copies and pastes cells in a column that contain #VALUE! into another sheet, and all the copies from one sheet are in one row on the paste sheet.

Basically I need it to read like this:

Loop through each sheet in the workbook
If the sheet tab is yellow then
Copy cell A5 and paste in the first empty column in the last sheet
Loop through K7:K40 in the original sheet
if any cell in K7:K40 is #VALUE! then
copy offset(0, -10) and paste in the cell next to where A5 was pasted
Go to the next sheet and copy and paste everything in the next row down.

So my final sheet will look like this:

	A	B	C	D
1	(Pasted A5 Data from Sheet1)	(Pasted K cell Data from Sheet1)	(Pasted K cell Data from Sheet1)	(Pasted K cell Data from Sheet1)
2	(Pasted A5 Data from Sheet2)	(Pasted K cell Data from Sheet2)	(Pasted K cell Data from Sheet2)	(Pasted K cell Data from Sheet2)
3	(Pasted A5 Data from Sheet3)	(Pasted K cell Data from Sheet3)	(Pasted K cell Data from Sheet3)	(Pasted K cell Data from Sheet3)

I am having a hard time trying to explain exactly what I want, but if anyone understands this I would greatly appreciate your help.

Holy moley you are a life saver! Thank you so much!