How to transfer data from Excel to PowerPoint worksheet and update PowerPoint Chart range?

Question

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:

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.
2. 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.

EDIT 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.

Answer 1

Sorry but I do not have enough reputation to add a comment.

Is there a reason that it cannot use linked data instead?

I currently have powerpoint shows that use excel data and just have it linked to pull through the most recent information when I select update links on open.

Thanks