"Method ‘Range’ of object ‘_Worksheet’ failed" error message in Excel/PowerPoint VBA

Greetings,

I've written a macro that creates PowerPoint charts from data in an Excel workbook.

It is nearly functional, except for a

Method ‘Range’ of object ‘_Worksheet’

failed error message.

The macro is run in a PowerPoint instance with no slides. It creates the correct PowerPoint chart with the data from the first Excel workbook sheet, but fails at this line

pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn))

when it gets to the second sheet in the Excel workbook. Specifically it says method

'Cells' of object '_Global' failed'

when I hover over the line in the VBA editor. Also,

LastRow

and

LastColumn

still refer to the 1st worksheets data range.

This leads me to believe that my:

LastRow = xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).Row
                        
LastColumn =xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).Column

References are not properly cycling through to the next sheet in the Excel workbook. This is weird because when I use a static range such as in

pptWorkSheet.Range("A1:B5")

, the macro goes through the sheets as it should.

I greatly appreciate any help. My full macro is below:

    Sub CreateChartAllWKsv6()
     ''''' Cleaned (i.e. syntax) version of v5
     '''''Impt
     '''This macro can
     ''' 1.add charts of different types
     ''' 2. resize chart data range to fit worksheet data
     
        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:\filepath\ExcelData_PPTChartIteration.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) 'Slide types:
                            ActiveWindow.View.GotoSlide ActivePresentation.Slides.Count
                    ' Create the chart and set a reference to the chart data.
                            Set myChart = CurSlide.Shapes.AddChart(xlColumn).Chart 'Chart types: https://msdn.microsoft.com/en-us/library/office/ff837417.aspx
                            Set pptChartData = myChart.ChartData
                    ' Set the PowerPoint Workbook and Worksheet references.
                            Set pptWorkBook = pptChartData.Workbook
                            Set pptWorkSheet = pptWorkBook.Worksheets("Sheet1") 'From Update
                    '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.
                            pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn)) '<~ Macro breaks down here on second sheet. Works perfect on 1st sheet
                            pptWorkSheet.Range("a2:z100").Value = xlWS.Range("a2:z100").Value '!!! IMPORTANT: FOR SOME REASON YOU CANNOT PUT A REFERENCE TO A2 OR THE CHART RANGE WILL RESIZE BACK TO THE DEFAULT. I SUSPECT THIS IT BECAUSE OF THE SERIES NAME
                    ' Apply styles to the chart.
                            With myChart
                                    .ChartStyle = 4
                                    .ApplyLayout 4
                                    .ClearToMatchStyle
                            End With
                    'Add Title
                            myChart.HasTitle = True
                    'Format title
                            With myChart.ChartTitle
                                .Characters.Font.Size = 18
                                .Text = "Test Chart"
                            End With
                    ' Add the axis title.
                            With myChart.Axes(xlValue)
                                    .HasTitle = True
                                    .AxisTitle.Text = "Units"
                            End With
                    'Apply data labels
                    myChart.ApplyDataLabels
                    'Close PowerPoint worksheet
                    myChart.ChartData.Workbook.Close
                    
            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
            'xlApp.Quit
    End Sub

Thanks! Worked. Can't believe I missed that.