Results 1 to 3 of 3

Error Adjusting Shape.Top Property when using VBA to copy from Excel to Powerpoint

Threaded View

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    Office 365
    Posts
    67

    Error Adjusting Shape.Top Property when using VBA to copy from Excel to Powerpoint

    I am working on a project that takes calendar information from excel, creates a powerpoint, and copies the data from excel into the powerpoint.

    I am having trouble standardizing the Shape.Top position for a range that represents employee anniversary dates. In my code I say
    .Top = 0.25 * 72
    I would like the range to be pasted 0.25" from the top of the slide, but when the powerpoint is created the areas land at varying horizontal positions from the top, and not at 0.25".

    With other shapes it has worked fine, but this range the height changes depending on the number of employees that have anniversaries in that month, so I feel like it may have something to do with that? But I'm not sure how to fix it. Perhaps shape.top isn't the best method to use in the case?

            
            '[Add the anniversary dates to the calendar]
            Sheets(monthsArray(i)).Range("J7:J30").ClearContents
            x = 7 'print row start for anniversary data
            
            'Summarize Anniversaries by Month in each sheet
            Do While j <= anniversaryEnd
                If a.Cells(j, 5) = i + 1 Then 'if the anniversary month matches the current month
                    'print info
                    Sheets(monthsArray(i)).Cells(x, 10) = "(" & a.Cells(j, 6).Value & ") " & a.Cells(j, 2).Value & " " & a.Cells(j, 1).Value & " - " & a.Cells(j, 4).Value & " " & a.Cells(j, 8).Value
                    x = x + 1
                    j = j + 1
                Else
                    Exit Do
                End If
            Loop
            
            '!!!!THIS IS THE PART I NEED HELP WITH!
            'Copy/Paste to powerpoint slide
            monthAnnEnd = Sheets(monthsArray(i)).Cells(6, 10).End(xlDown).Row 'get ending row for this month
            Sheets(monthsArray(i)).Range("J6:J" & monthAnnEnd).Copy
            mySlide.Shapes.PasteSpecial
            With mySlide.Shapes(4)
                .Width = 1.57 * 72
                .Height = (monthAnnEnd - 6) * 0.32 * 72 'variable height based on number of entries
                .Left = 2.9 * 72
                .Top = 0.25 * 72
            End With
    I have included the spreadsheet with personal information removed for reference. The macro is called CreatePowerPoint.
    Attached Files Attached Files
    Last edited by kelseygueldalewis; 02-05-2020 at 03:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Help on Macro to Copy Shape and Paste into PowerPoint
    By Mysore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2020, 01:42 AM
  2. [SOLVED] copy past table from excel to powerpoint - keep powerpoint formatting
    By djc225 in forum PowerPoint Formatting & General
    Replies: 2
    Last Post: 10-30-2018, 11:32 AM
  3. vba copy excel range to powerpoint ran error
    By ermai in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2016, 08:00 AM
  4. Adjusting Pivot Table to Generate grpah then copy and paste graph into PowerPoint
    By flutergirl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-18-2014, 11:36 PM
  5. Image to PowerPoint Shape from Excel Path in EXCEL VBA. & BOLD Part of String
    By aurorajones in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2014, 01:14 AM
  6. [SOLVED] Excel run-time error '381': Could not set the List property. Invalid property array index
    By eemiller1997 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-20-2012, 12:48 PM
  7. Error setting shape text property
    By Sarge in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-10-2006, 11:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1