Excel VBA copy and paste Loop doesn't copy new range

[kurombo]

Hi all,

I have been working on the following code to copy paste the following:
- grab the header (Row 1)
- grab each group of data in pairs of 5 (Row 2-6, 7-11, 12-16, etc.)
- paste into Powerpoint slide

I have written the following so far:
'grab header row
Set head = ThisWorkbook.ActiveSheet.Range(Cells(1, "A"), Cells(1, "C"))

'loop through each data
For i = 2 To last Step 5
Set rng = ThisWorkbook.ActiveSheet.Range(Cells(i, "A"), Cells(i + 4, "C"))
Set rng = Union(head, rng)
'Copy Excel Range
rng.Copy
'Create new slide
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile

Next i


Problem: What this is doing is looping through the data, grabbing the header and the 5 rows of data. Then on the next loop, it keeps the previous set of data and copies and pastes the next set of data, so I have 10 rows of data instead of 5.

Can you help explain why my code is not copying the next set of data? It seems like it still recognizes "i" as 2, while only "i+4" is iterative. I have already tried clearing the clipboard with the "Application.CutCopyMode = False" method, but that did not help.

Thanks for any suggestions.

 

[BrianJN1]

You want your For .... Next to start at 2 and end at "last" but you have not defined a value for "last".

 

[kurombo]

You want your For .... Next to start at 2 and end at "last" but you have not defined a value for "last".

Sorry I didn't include it in my message earlier. My "last" is a Double that counts however many rows are in my Excel chart

It's simply:
'Find number of rows
With ActiveSheet last = Cells(Rows.Count, "A").End(xlUp).Row
End With

 

[BrianJN1]

Is this part correct: Set mySlide = myPresentation.Slides.Add(1, 11) '1 ?
I am wondering if that "1" should not be "i".
After that I am out of suggestions, sorry.

 

[MARK858]

Obviously check, test and answer BrianJN1 from the previous post first as I have similar thoughts.

First though why the With statement? you aren't using it?

i increments correctly for me.
Are you sure that it isn't the paste that is incorrect and you aren't overwriting it as the below copies to another sheet fine for me?

Sub xxx()
    'With ActiveSheet
    last = Cells(Rows.Count, "A").End(xlUp).Row
    'End With
    'grab header row
    Set head = ThisWorkbook.ActiveSheet.Range(Cells(1, "A"), Cells(1, "C"))

    For i = 2 To last Step 5
        Set Rng = ThisWorkbook.ActiveSheet.Range(Cells(i, "A"), Cells(i + 4, "C"))
        Set Rng = Union(head, Rng)
        'Copy Excel Range
        Rng.Copy
        'Create new slide
        'Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
        Sheets(2).Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial
Debug.Print i
    Next i

End Sub

 

[kurombo]

Is this part correct: Set mySlide = myPresentation.Slides.Add(1, 11) '1 ?
I am wondering if that "1" should not be "i".
After that I am out of suggestions, sorry.

That's just saying to create Slide 1 in powerpoint, which is another thing I need to fix after this.

My loop is always creating Slide 1, so I need to put in a counter there.

 

[Peter_SSs]

I'm not an expert in this area, but I think the problem might be in copying a disjoint range to PPT.
This seemed to work for me: hide all the data rows and unhide each section to copy/paste

For i = 2 To last Step 5
  Rows("2:" & last).Hidden = True
  Set Rng = ThisWorkbook.ActiveSheet.Range(Cells(i, "A"), Cells(i + 4, "C"))
  Rng.EntireRow.Hidden = False
  Set Rng = Union(head, Rng)

 

[kurombo]

I'm not an expert in this area, but I think the problem might be in copying a disjoint range to PPT.
This seemed to work for me: hide all the data rows and unhide each section to copy/paste

For i = 2 To last Step 5
  Rows("2:" & last).Hidden = True
  Set Rng = ThisWorkbook.ActiveSheet.Range(Cells(i, "A"), Cells(i + 4, "C"))
  Rng.EntireRow.Hidden = False
  Set Rng = Union(head, Rng)

Sorry for the late reply.

This worked! Absolutely incredible...I would never have thought about hiding rows.

I really appreciate your help.

And to others who have this problem or are googling it, the code will let you take a header and repeatedly paste it to data set in your chart in Powerpoint or wherever you want to paste it to.

For i = 2 To last Step 5
Rows("2:" & last).Hidden = True
Set Rng = ThisWorkbook.ActiveSheet.Range(Cells(i, "A"), Cells(i + 4, "C"))
Rng.EntireRow.Hidden = False
Set Rng = Union(head, Rng)
Rng.Copy

As an added bonus, this is what I used to make sure I'm pasting it to the next Powerpoint slide:

ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)

Thanking everyone in this thread regardless of solution for chiming in

 

[Peter_SSs]

This worked! Absolutely incredible...I would never have thought about hiding rows.

I really appreciate your help.

You are welcome. I learned something new too.