Copy excel to PowerPoint with Dynamic Range

Hi,

I've sourced some bits of code from various, helpful forums that will copy a range from excel to a new Powerpoint file. The first section of code below works perfectly (Sub ExcelToNewPowerPoint(), but requires a specified range, e.g. A1:J28, whereas the table I am using is dynamic and will change according to filters used in the workbook. The solution I am aiming for is to allow the user to set the filters in the workbook and then hit a button to copy the data into a new PowerPoint slide.

Can someone help me change the code so that the range is dynamic, so that it copies only cells with data in them. I have found some code below (Sub DynamicRange() that uses the LastRow and Last Column functions - but despite my best efforts I cant seem to combine the two.

Thanks!


Sub ExcelToNewPowerPoint()
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Create instance of PowerPoint
Set PPApp = CreateObject("Powerpoint.Application")

' For automation to work, PowerPoint must be visible
' (alternatively, other extraordinary measures must be taken)
PPApp.Visible = True

' Create a presentation
Set PPPres = PPApp.Presentations.Add

' Some PowerPoint actions work best in normal slide view
PPApp.ActiveWindow.ViewType = ppViewSlide

' Add first slide to presentation
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)

'Step 4: Copy the range as a picture
Sheets("Data").Range("DynamicRange").CopyPicture _
Appearance:=xlScreen, Format:=xlPicture

'Step 5: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

'Step 6: Add the title to the slide
SlideTitle = "My First PowerPoint Slide"
PPSlide.Shapes.Title.TextFrame.TextRange.Text = SlideTitle

'Step 7: Memory Cleanup
PP.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set PP = Nothing

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

Sub DynamicRange()

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Data")
Set StartCell = Range("A1")

'Refresh UsedRange
Worksheets("Data").UsedRange

'Find Last Row and Column
LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

'Select Range
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

End Sub

hi
try replacing your code at step 4 with something like this (assumes you are copying from the active sheet). For neatness, I'd move the declarations to the start.

Please Login or Register  to view this content.

Hi.

Thanks so much, that works very well.

Would it be possible to put the button on another worksheet ("PIVOT") as the button gets moved around a lot as the table changes size? I added the line Set sht = Worksheets("Grid") below to specify the sheet the range should be taken from, but when I hit the button it takes the range from the sheet the button is on.

Please Login or Register  to view this content.

Hi
this version should work any where in the workbook, assuming your range to copy is on a sheet named "data"

Please Login or Register  to view this content.

Thanks NickyC - you're a legend!

One more query, if its not too much to ask...

As the grid is dynamic the range above seems to pick up all of the cells that are affected, so the image of the grid that gets pasted into PowerPoint has a lot of blanks below it if the grid is of a smaller group. Is there anyway of amending the code so that it only picks up cells that have text in them?

Is there any way to tell excel which range you want to pick? The code above assumes you want the whole of the used range on the data sheet and any cells above it. Even with a dynamic range, it may be possible to be more specific than that - for example, if your grid has fixed headers you could apply a name to the first cell in the header row and make that the first cell of the range to copy.

As a last resort, we could temporarily take the users to the data sheet, get them to input the range in an input box, then take them back to where they started, but it would be clunky.

Its probably best if I show you the worksheet (attached). The user will use the slicers on the Pivot tab to select the population and this then changes the table on the Grid tab. The headings will always be the same, but the size between each section will vary quite a lot depending on the number of names in the group selected. I just want the image that is pasted into the PowerPoint to be from "B2" to the last name in section 9.

I hope that makes sense and thanks again for your help with this.

I think the problem could be as simple as me thinking you wanted to copy from sheet "data" not sheet "grid".

Having looked at your layout I think we could simplify things a lot

Please Login or Register  to view this content.

Or, if you don't need the while "border" in your picture provided by column A and row 1:

Please Login or Register  to view this content.

Hi.

Sorry, I probably didn't explain that very well in the first place! This now works perfectly and will save me a lot of time!

Thanks again for all of your help!