1

currently I am using a predefined solution where a large table that is too big for a slide is split into 20 rows, the header is deleted and then all 20 rows as well as the header are copied on a new PowerPoint slide until the entire table is split (this is done in Excel).

The important part of the code looks like this:

Set wb = Workbooks("Planning.xlsm")
Set ws = wb.Sheets("LastYear")
Set wss = wb.Worksheets.Add
Set PowerPointApp = GetObject(class:="PowerPoint.Application")
Set myPresentation = PowerPointApp.ActivePresentation

Do While i <= LastRow
    j = Application.Min(i + 20, LastRow)
    Union(rngH, ws.Range("A" & i, ws.Range("M" & j))).Copy
    wss.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    wss.Range("A1").PasteSpecial Paste:=xlPasteValues
    wss.Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme

    Set sld = myPresentation.Slides.Add(myPresentation.Slides.Count + 1, ppLayoutBlank)
    wss.Range("A1:M" & j - i + 2).Copy
    
    sld.Shapes.PasteSpecial DataType:=ppPasteHTML, Link:=msoFalse

    'Here title of the table & format is added
    Set Header = sld.Shapes.AddShape(msoShapeRectangle, 0, 0, 960, 40)
    With Header
        .Fill.ForeColor.RGB = RGB(0, 32, 91)
        .Line.Visible = False
    End With
Loop

Afterwards I activate a macro in PowerPoint, which formats the table to the correct size, so that the entire space can be used except for the title.

The problem is that the rows can have different heights and therefore sometimes a table is too short or too long, so that a manual rework has to be done. Is it possible to split and copy the table based on the PowerPoint slide height and the summation of the row heights (something like, add rows until row height sum equals slide sum)? I have already tried a few things myself but I am not getting anywhere.

Thanks a lot for your help/hints!

braX
  • 11,506
  • 5
  • 20
  • 33
Tim007
  • 11
  • 2

0 Answers0