Hi! e
I would like to update title page on a PowerPoint presentation with the month end date, referencing a range in Excel that will be dynamic with the =TODAY function from another cell. i.e. today's date is 4/26/21, user would be creating a presentation for prior month end, set to "March 2021". I have written the code a couple of different ways; one with the actual month end / year in the code, formatting will adapt to the presentation, which is what I need. However, when I write VBA to find the range, copy, paste special - the formatting gets dropped. Unfortunately, I don't know the specifics of the formatting (color, etc). The presentation continually references this month end date, so it would be ideal to set it as a variable to continually reference, rather than entering formatting directives at every paste (even if I knew them....) Item 4) is where it falls apart. How do I enter a paste command using a variable into PPT?
Thank you so much!
I would like to update title page on a PowerPoint presentation with the month end date, referencing a range in Excel that will be dynamic with the =TODAY function from another cell. i.e. today's date is 4/26/21, user would be creating a presentation for prior month end, set to "March 2021". I have written the code a couple of different ways; one with the actual month end / year in the code, formatting will adapt to the presentation, which is what I need. However, when I write VBA to find the range, copy, paste special - the formatting gets dropped. Unfortunately, I don't know the specifics of the formatting (color, etc). The presentation continually references this month end date, so it would be ideal to set it as a variable to continually reference, rather than entering formatting directives at every paste (even if I knew them....) Item 4) is where it falls apart. How do I enter a paste command using a variable into PPT?
VBA Code:
Sub OpenPPTTemplate()
'1.Declare Variables
Dim papp As PowerPoint.Application
Dim ppt As PowerPoint.Presentation
Dim psld As PowerPoint.slide
Dim psh As PowerPoint.Shape
Dim rng as Range
'2. create a new instance of powerpoint and Open Template Presentation
Set papp = New PowerPoint.Application
Set ppt = papp.Presentations.Open("C:Path")
'3. Set Slide in PPT
Set psld = ppt.Slides(1)
Set psh = psld.Shapes("Title")
'4. Set the range and copy
set rng = Workbooks("Template_VBA.xlsm").Worksheets("Char").Range("G19")
rng.copy
psh.TextFrame.TextRange.PasteThank you so much!
Last edited by a moderator:
