I've created a macro in PowerPoint that opens up an Excel workbook, loops through the sheets in the workbook, creates PowerPoint charts and populates them with the data from the Excel sheets. Just to make it clear, the macro is run from PowerPoint.

I now have the need to make the data ranges(when transferring from Excel to Powerpoint worksheets) and PowerPoint chart data ranges dynamic. E.g. because each Excel worksheet range is not the same and therefore each PowerPoint chart data range is not the same.

Below is my macro:
Please Login or Register  to view this content.
I'm running into 2 issues:

Please Login or Register  to view this content.
and
Please Login or Register  to view this content.
transfer data to the first PowerPoint worksheet, but fail on the second - nothing is pasted.
Please Login or Register  to view this content.
fails to resize to PowerPoint chart range on the PowerPoint worksheet. I get a method failed error.

My workaround for the first issue is to just transfer a large range that my data will never be larger than using
Please Login or Register  to view this content.
As for my second issue, I've also tried
Please Login or Register  to view this content.
just to see if I can even control the datasource, which I got from here. Nothing works. I'm beginning to think this is impossible.

My references for how to work with PowerPoint charts were this. I am running Office 2013.