Need help with getting variables filled properly.

Dear forum readers,
I am having a problem with a VBA program that I wrote (Excel-VBA).

To start with some context.
I am a newbie when it comes to programming in VBA, so that been said.. 
I work in a company that uses a lot of individual files, called bricks.
All bricks (Excel-files) reside in one folder.

Each brick (individual file) has a title and subtitle (always the first and second filled row) and a type of the status of the product: (Mainstream, Emerging (R & D), Containment, Retirement).

For example:
File(s) --> Brick: Computer client operating system.xls

Content:
- Title: Client computers
- Subtitle: Operating systems

Followed by the status:
- Emerging (R & D)
o Windows 1010
o Apple IOS 398393
- Mainstream
o Windows 10
o Apple 123
- Containment
o Windows 7
- Retirement
o Windows 98
o Commodore 64
o MSX 2


I ‘wrote’ a program that:
- Reads all the Excel files in a specific folder
- Reads the title and subtitle
- Reads all rows between (for example) Mainstream and Emerging
- Puts all gathered information in ONE destination Excel-file (i.e. Title, Subtitle and information between for instance Mainstream and emerging)

So here is my/the challenge.
1. Sometimes people are filling the title and subtitle a few rows higher or lower, but it’s always (title first followed by a subtitle).
o I like to read the first and second non-blank rows.
o If someone forget to put in a title or subtitle I like to fill the variable with “No title or subtitle found”
2. Sometimes people are filling information (Emerging, Mainstream, Containment, et cetera) in column B instead or A.
o I like to fetch the information between (for example) Information and Emerging (the row), even if somebody puts the information in column B, C, D et cetera.

My ‘program’ works, but not always (sometimes it skips information/row).
And I know, it looks more like a spaghetti program, so my question is.
Can anyone help me with this challenge and provide me with some code that is easy to interpret. 

I added the file (Files have to be placed in C:\Temp\Excel)
The code resides in file: Destination_File_Summary.xls

Thank you very much for assistance.

Is this okay now?

Nobody?