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