1/.
The second line of the code (Range("G2").....) pastes the values in Sheet 2, which is where a form button is located with this macro assigned to it.
That's where you ask for it to go! Shift the button, or change the destination cell.

2/.
as the macro is running, it tries to open a file three different times
Check the number of times your formulae calls 'Sheet 1' instead of 'Sheet1', this looks like the source of this error.

Rather than clutter the BB with useless code, I have edited Post #2.
I have corrected what I see as errors, and simplified as much as I can confidentlly can.

Check that revised code out

Hope this helps