VBA HLOOKUP and Paste Special with Values and Number Format

I have a macro that currently uses an HLOOKUP to get a value (single cell) from one workbook and set that value in to a second workbook. I thought my method was working great until I noticed I have a few EURO values mixed in with mostly USD numbers. I need to figure out how capture the value and format and do a PasteSpecial in to my new workbook.

My current code looks like this;

Please Login or Register  to view this content.

I'm thinking I need to find a way to get the cell address of the HLOOKUP and from there I can copy that cell and do a PasteSpecial. Something like this in theory;

Please Login or Register  to view this content.

I've tried a few different things and have come up short on all of them. Any help/suggestions would be appreciated.

Thanks,
--Razor

Thanks, allow me to prepare something and I will upload.

So I had time to put some stuff together. My issue, how do I copy the information from the data set files, and paste them in to the report spreadsheet matching the source format?
In this example sites 1, 2, 5, and 6 are USD. Sites 3, 4, and 7 are in Euro's (highlighted orange) but getting copied in as USD. I'm assuming I need to do some type of PasteSpecial, but I can't figure it out.

How my project is designed to run:

1. The data set files go in their own sub folder.
2. Open the report file.
3. With the report file open, run the sub BuildDatasheet from the VBA editor.

Note: I commented out a sort and some additional work that is done after the data is brought in to the worksheet. I had to do some work to remove identifying code/notes/data, etc. Hopefully the important stuff is still easy to follow. Please let me know if there are any questions.