Copying Range from Excel and Paste as Table Keeping Source Formatting

fronzenite

New Member
Joined
Jun 3, 2018
Messages
1
Hi all,

I'm pretty new here. I have been struggling for quite awhile with a present code which i would highly appreciate if i could get some help.

I have a workbook with multiple sheets on it, and would like the VBA code to loop through all the worksheets, copying table from two cell ranges and pasting them into powerpoint, while keeping source formatting (so that the two tables will be in table form which is editable), as well as positioning one table left, and the other to the right.

As of now, i have managed to come up with the following code, but the loop does not seem to work.

Thanks in advance!

Code:
Sub WorkbooktoPowerPoint()

    'Step 1: Declare your variables
    Dim pp As Object
    Dim PPPres As Object
    Dim PPSlide As Object
    Dim xlwksht As Worksheet
    Dim MyRange As String
    Dim MyRange1 As String 'Define another Range
    Dim MyTitle As String


    'Step 2: Open PowerPoint, add a new presentation and make visible
    Set pp = CreateObject("PowerPoint.Application")
    Set PPPres = pp.Presentations.Add
    pp.Visible = True


    'Step 3: Set range for Both Tables here
    MyRange = "B15:F40"
    MyRange1 = "H15:L42"
    
    'Step 4: Start the loop through each worksheet
    For Each xlwksht In ActiveWorkbook.Worksheets
    xlwksht.Select Application.Wait(Now + TimeValue("0:00:5"))
    
    'Step 5: Copy First Table Range
     xlwksht.Range(MyRange).Copy
    
    'Step 6: Count slides and add new blank slide as next available slide number '(the number 12 represents the enumeration for a Blank Slide)
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
    PPSlide.Select
    
    'Step 7: Paste First Table Range into PPT
    pp.CommandBars.ExecuteMso ("PasteSourceFormatting")
    
    'Step 8: Copy Second Table and Paste Range into PPT
    xlwksht.Range(MyRange1).Copy
    pp.CommandBars.ExecuteMso ("PasteSourceFormatting")
    
    Next xlwksht
    
    'Step 9: Memory Cleanup
    pp.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set pp = Nothing


    End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top