Help With Excel Object Library - Worksheet & Range

[Mfrosty]

Hey guys,

Not really familiar with the excel object library so I am struggling with some basic syntax that I hope is a quick fix and a "Doh!" moment.


Goal: Take my 10 worksheets that have a mix of charts/text/images but in the ranges of A1:O37 in an excel workbook and dump each one into its own powerpoint slide. Seems simple enough right? I bolded the parts where I think my problems are.

Here is my code:

Sub ExceltoPPT()

        Dim newPPT As PowerPoint.Application
        Dim slidePPT As PowerPoint.Slide
[SIZE=3][B]        Dim sheetXLS As Excel.Worksheet[/B][/SIZE]
     
        On Error Resume Next
        Set newPPT = GetObject(, "PowerPoint.Application")
        On Error GoTo 0
     
        If newPPT Is Nothing Then
            Set newPPT = New PowerPoint.Application
        End If


        If newPPT.Presentations.Count = 0 Then
            newPPT.Presentations.Add
        End If
     


        newPPT.Visible = True
    
[U]    'This is where I want it to go through each worksheet & copy the range A1:O37. Should the syntax be something like ActiveSheet.xxx?[/U]
        For Each sheetXLS In Range("A1:O37")
        
        'Adds a new slide for each worksheet found
            newPPT.ActivePresentation.Slides.Add newPPT.ActivePresentation.Slides.Count + 1, ppLayoutText
            newPPT.ActiveWindow.View.GotoSlide newPPT.ActivePresentation.Slides.Count
            Set slidePPT = newPPT.ActivePresentation.Slides(newPPT.ActivePresentation.Slides.Count)
                
[U]        'Worksheet gets copied hopefully. What do I put for the spot I bolded? Name of file or each worksheet?[/U]
[SIZE=3][B]            Page1xls.Select[/B][/SIZE]
            ActiveChart.ChartArea.Copy
            slidePPT.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
    
        newPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        newPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True


        Next
     
    AppActivate ("Microsoft PowerPoint")
    Set slidePPT = Nothing
    Set newPPT = Nothing
     
End Sub

Any help would be greatly appreciated. I am sure with enough trial and error I will figure it out regardless. thanks!