Transfer Excel data with dynamic ranges to PowerPoint worksheet and update PowerPoint chart range

[suremac]

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:

Sub CreateChartAllWKsv3()
       
        'Create variables
            Dim myChart As Chart
            Dim pptChartData As ChartData
            Dim pptWorkBook As Excel.Workbook
            Dim pptWorkSheet As Excel.Worksheet
            Dim xlApp As Excel.Application
            Dim xlWB As Workbook
            Dim xlWS As Worksheet
            Dim CurSlide As Slide 'new from update
            Dim LastRow As Long ' 8/22
            Dim LastColumn As Long ' 8/22
            
        ' Create new excel instance and open relevant workbook
            Set xlApp = New Excel.Application
            xlApp.Visible = True 'Make Excel visable
            Set xlWB = xlApp.Workbooks.Open("C:\ExcelWorkbook.xlsm", True, False)  'Open relevant workbook
            
        'Loop through each worksheet in xlWB and transfer data to new pptWorkBook and
        'create new PowerPoint chart
                For Each xlWS In xlWB.Worksheets
        
                        'Add a new slide where we will create the PowerPoint worksheet and chart                            
                                Set CurSlide = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutText)
                                ActiveWindow.View.GotoSlide ActivePresentation.Slides.Count
                        ' Create the chart and set a reference to the chart data.
                                Set myChart = CurSlide.Shapes.AddChart.Chart 'changed 8/19
                                Set pptChartData = myChart.ChartData
        
                        ' Set the PowerPoint Workbook and Worksheet references.
                                Set pptWorkBook = pptChartData.Workbook
                                Set pptWorkSheet = pptWorkBook.Worksheets("Sheet1") 
                        'Clear contents from PowerPoint worksheet
                                pptWorkSheet.UsedRange.ClearContents 'Works
                        'Find Last Row and Column of xlWS
                                LastRow = xlWS.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row
                                LastColumn = xlWS.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Column
                        ' Add the data to the PowerPoint workbook.
                                xlWS.Range(Cells(1, 1), xlWS.Cells(LastRow, LastColumn)).Copy 'Fails to past any data on the second worksheet
                                pptWorkSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
                        ' Update PowerPoint workbook chart data reference.
                                 'line below didn't work                            
                                pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range("Table1[#All]").Resize(Rows.Count, Columns.Count)
                                
                        ' Apply styles to the chart.
                                With myChart
                                        .ChartStyle = 4
                                        .ApplyLayout 4
                                        .ClearToMatchStyle
                                End With
        
                        ' Add the axis title.
                                With myChart.Axes(xlValue)
                                        .HasTitle = True
                                        .AxisTitle.Text = "Units"
                                End With
        
                        'Apply data labels
                                myChart.ApplyDataLabels
             Next xlWS
        
        ' Clean up the references.
                Set pptWorkSheet = Nothing
        ' pptWorkBook.Application.Quit
                Set pptWorkBook = Nothing
                Set pptChartData = Nothing
                Set myChart = Nothing
        'Clean up Excel references.
                Set xlApp = Nothing
        'Option to close excel workbook
                xlWB.Close
                'Option to close the excel application
        End Sub

I'm running into 2 issues:

[LIST=1]
[*]xlWS.Range(Cells(1, 1), xlWS.Cells(LastRow, LastColumn)).Copy

and

pptWorkSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

transfer data to the first PowerPoint worksheet, but fail on the second - nothing is pasted.

[*]pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range("Table1[#All]").Resize(Rows.Count, Columns.Count)

fails to resize to PowerPoint chart range on the PowerPoint worksheet. I get a method failed error.
[/LIST]

My workaround for the first issue is to just transfer a large range that my data will never be larger than using

pptWorkSheet.Range("a1:z100").Value = xlWS.Range("a1:z100").Value.

As for my second issue, I've also tried

myChart.SetSourceData Source:="='Sheet1'!$A$1:$B$5", PlotBy:=xlColumns

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.

 

[Rijnsent]

Hi Suremac,

I suspect that some references are not exact enough:

pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range("Table1[#All]").Resize(Rows.Count, Columns.Count)
'-> both Rows.Count and Columns.Count "don't know" what they refer to (what sheet/range):

Set Tbl = pptWorkSheet.ListObjects("Table1")
Tbl.Resize(pptWorkSheet.Range("Table1[#All]").Resize(Tbl.Rows.Count, Tbl.Columns.Count)

Hope that helps,

Koen