Simple Excel VBA Data Manipulation

I have a spreadsheet (Data Count [login to view URL]). The first 2 tabs will be populated via copy and paste from a webapp we use and will follow that format. From those tabs I need the information to be consolidated and output as is shown in the third tab. When the process is live the number of rows will vary (it is the output from a program that counts data based on criteria entered, therefore the rows will change depending on the criteria selected) so it needs to be dynamic. I have attached 2 examples of how the files will be in their raw state. There are some additional instructions in the Instructions tab including some data transformation. This is not to be included in the final document, it is just there for information. The final step is to export the PDF tab to PDF and auto-naming the file as the date and time. I would like this to be a prompt with the name auto populated and just prompting for a folder location.