Need help with combining data from irregularly structured worksheets under specific header

Hello,

I'm new to the forum, so please pardon me if I accidentally annoy any of you.

I’m looking for a solution to the following problem: I have a workbook with various worksheets having different field structures, and repeats of headers within the same worksheet.

I’ve attached a copy of the example worksheet to help illustrate the issue. I’ve tried looking through various solutions for merging columns, but the solutions seem to involve copying entire columns, or depend on a regular field structure. This is impractical for my task, so perhaps you could help.

For the output, I’d need my VBA to:
Go through all the worksheets, find a header and collate all the values under the specified header. For example, if I wanted a list of all Eggs and Frogs in the workbook, I’d need to look under each worksheet, in all the cells under the Eggs and Frogs headers and store them in a separate newly generated workbook (or worksheet if impractical)

The sequence is unimportant as it is just a lump of data for an importer tool, but the Eggs and Frogs fields need to be properly aligned as they appear in the raw data.

example sheet.xlsx