Macro for excel and powerpoint

[malezlotko]

Hi All,

First off let me say how useful this forum has been thus far.

I'd like to get a macro to copy the user defined print area to a ppt slide. For example: if an excel user goes to page setup and selects FitToPagesWide = 1 and FitToPagesTall = 1 the macro will copy that area and paste it to ppt.

Code below works for one worksheet but not for others?

Thanks in advance for the help.

-Patryk

Sub Copy_Paste_to_PowerPoint()
     
     'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
    Dim ppApp As PowerPoint.Application
    Dim PPSlide As PowerPoint.Slide
     
     'Original code sourced from Jon Peltier http://peltiertech.com/Excel/XL_PPT.html
     'This code developed at http://oldlook.experts-exchange.com:8080/Applications/MS_Office/Excel/Q_21337053.html
     
    Dim SheetName As String
    Dim TestRange As Range
    Dim TestSheet As Worksheet
    Dim TestChart As ChartObject
     
    Dim PasteRange As Boolean
    Dim RangePasteType As String
    Dim RangeName As String
    Dim AddSlidesToEnd As Boolean
     
     'Parameters
     
     'SheetName           - name of sheet in Excel that contains the range or chart to copy
     
     'PasteRange          - If True then Routine will copy and Paste a range
     'RangePasteType      - Paste as Picture linked or unlinked, "HTML" or "Picture". See routine below for exact values
     'RangeName           - Address or name of range to copy; "B3:G9" "MyRange"
     'AddSlidesToEnd      - If True then appednd slides to end of presentation and paste.  If False then paste on current slide.
     
     'use active sheet. This can be a direct sheet name
    SheetName = ActiveSheet.Name
     
     'Setting PasteRange to True means that Chart Option will not be used
    PasteRange = True
    RangeName = ActiveSheet.PageSetup.PrintArea
    RangePasteType = "picture"
    RangeLink = True
     
    AddSlidesToEnd = True
     
     
     'Error testing
    On Error Resume Next
    Set TestSheet = Sheets(SheetName)
    Set TestRange = Sheets(SheetName).Range(RangeName)
    Set TestChart = Sheets(SheetName).ChartObjects(ChartNumber)
    On Error GoTo 0
     
    If TestSheet Is Nothing Then
        MsgBox "Sheet " & SheetName & " does not exist. Macro will exit", vbCritical
        Exit Sub
    End If
     
    If PasteRange And TestRange Is Nothing Then
        MsgBox "Range " & RangeName & " does not exist. Macro will exit", vbCritical
        Exit Sub
    End If
     
     'Look for existing instance
    On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
     
     'Create new instance if no instance exists
    If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
     'Add a presentation if none exists
    If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add
     
     'Make the instance visible
    ppApp.Visible = True
     
     'Check that a slide exits, if it doesn't add 1 slide. Else use the last slide for the paste operation
    If ppApp.ActivePresentation.Slides.Count = 0 Then
        Set PPSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
    Else
        If AddSlidesToEnd Then
             'Appends slides to end of presentation and makes last slide active
            ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
            ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
            Set PPSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
        Else
             'Sets current slide to active slide
            Set PPSlide = ppApp.ActiveWindow.View.Slide
        End If
     End If
     'Options for Copy & Paste Ranges and Charts
    If PasteRange = True Then
         'Options for Copy & Paste Ranges
        If RangePasteType = "Picture" Then
             'Paste Range as Picture
            Worksheets(SheetName).Range(RangeName).Copy
            PPSlide.Shapes.PasteSpecial(ppPasteDefault, link:=RangeLink).Select
        Else
             'Paste Range as HTML
            Worksheets(SheetName).Range(RangeName).Copy
            PPSlide.Shapes.PasteSpecial(ppPasteHTML, link:=RangeLink).Select
    End If
    End If
         
     'Center pasted object in the slide
    ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
     
    AppActivate ("Microsoft PowerPoint")
    Set PPSlide = Nothing
    Set ppApp = Nothing

End Sub