VBA populate target cells getting data and reference from other sheet-CodePudding

I'm trying to copy a range from a looped iteration (The Range "M2:U11" changes based on the values of cell "A6") and paste them into a separate sheet in a well-arranged manner.

So I have a new sheet (let's name it PDF Template). I want to paste the relevant Ranges(Tables) and format/distribute them exactly as it is shown in this imagee>>>>:

well-arranged ranges

I don't know if it would be possible. Any answers?

CodePudding user response：

Private Sub CommandButton1_Click()

'Declare our Variables
Dim rR As Range, rA6 As Range
Dim powerpointapp As Object
Dim mypresentation As Object
Dim myslide As Object
Dim myshape As Object
Dim i As Integer


'assigning range into variable
Set rR = ThisWorkbook.Worksheets("Sheet1").Range("M1:U11")
Set rA6 = ThisWorkbook.Worksheets("Sheet1").Range("A6")     '<<< Is A6 also on this sheet? Else correct sheet name

'If we have already opened powerpoint
Set powerpointapp = GetObject(class:="PowerPoint.Application")
                  
'If powerpoint is not opened
If powerpointapp Is Nothing Then Set powerpointapp = CreateObject(class:="PowerPoint.Application")

'To create a new presentation
Set mypresentation = powerpointapp.Presentations.Add

 
'Loop through all the values in "A6"
         'If Range("A6") = loop 1 To 100 Then Range("M1:U11").Select
         'Copy the Selection
         'Paste each iteration on a new slide on Powerpoint
         
For i = 1 To 300    'Loop A6 through all values from 1 to 300
    rA6 = i

    Set myslide = mypresentation.slides.Add(1, 11)
    'Copy the puzzle
    rR.Copy
    
    'to paste range
    myslide.Shapes.PasteSpecial DataType:=2
    Set myshape = myslide.Shapes(myslide.Shapes.Count)
    myshape.Left = 250
    myshape.Top = 150
    
Next i
    
powerpointapp.Visible = True
powerpointapp.Activate

'to clear the cutcopymode from clipboard
Application.CutCopyMode = False

End Sub

CodePudding user response：

If you always have 4 sheets, you can just copy the range from each sheet, tell it where to paste it on the PDF sheet. Then copy the range on the PDF sheet that has all four of the sheets boxes before pasting it into your spreadsheet.