copy data from one excel worksheet to another automatically based on yes or no

My suspicion is that a re-design is called for here. But without seeing the actual workbook (or a mockup if the actual is confidential) it's hard to say.

The reason I suspect a re-design is called for: a well designed database could have all service jobs on a single sheet, and then use Excel's marvelous abilities to extract from that single database the ongoing. That would be a lot easier, frankly, and more robust/reliable, than doing the opposite--seeking to consolidate ongoing issues from a disparate set of individual tabs.

So if you could help us help you by sharing either the actual workbook or the mockup as described, that would go a long way toward answering a lot of questions.