So I have an issue that seems to be fairly simple, but little issues just keep popping up (Welcome to Excel, MrIceGuy!). Basically, I have around 20 Sharepoint files that are all structured the same, but with different data for different branches of our business. These files are populated by a dropdown menu to select the month, which will then populate a calendar of sorts for various KPI's.

I would like to write a script to parse through these files one by one, change the dropdown to the selected month, and copy the relevant data over to my existing Excel file (CopyToMe.xlsm).

I have the code to open each file and move on to the next -
Please Login or Register  to view this content.
for each file.

I can't seem to copy the data over, though. I mapped the Sharepoint to the Y drive to reference it more easily, and what I have right now is -

Please Login or Register  to view this content.
Any help would be greatly appreciated!