Excel VBA IF...THEN...ELSE for 3 options

I am having trouble getting my can to work. I have a workbook with four sheets. The first three sheets have a column where a user selects a dropdown YES or NO. The fourth sheet collects the rows in the first three sheets where there is a YES. These rows need to follow in order, i.e., those from Sheet 1, then those for sheets 2 and finally those from Sheet 3.

I am using IF then statements in the following manner and they aren't working: If sheet1colX=YES then ..... End IF If sheet2colX=yes then ..... End IF If sheet3colX=yes then ..... End IF

It is possible that they could be YES or no in any of the sheets or none at all.

Any ideas will be appreciated.

Thanks.