Adding a Loop to Code Work for Multiple Sheets

Question

I have been using this code which convert the single range to Power Point as Picture and the code is working fine. I want to add a loop on the code where it will work for multiple sheets.

I have Sheet Name in Col"A", Sheet Ranges in Col"B" and the Status is in Col"C".

Where If Col"C" cells are = "Include" then those sheets ranges will be paste as picture to Power Point and all other will be ignored.

Your help will be greatly appreciated.

Const ppFileName = "C:\Topline\Topline Writeup.pptx"

Dim PPT As Object
Set PPT = CreateObject("Powerpoint.Application")
PPT.Visible = True
' Use this if file already exists:
' PPT.Presentations.Open Filename:=ppFileName
' Use this if you want to create a new file:
PPT.Presentations.Add
PPT.ActivePresentation.slides.Add Index:=1, Layout:=12 

Worksheets("Pivot").Range("FC3:FP35").CopyPicture Appearance:=xlScreen, Format:=xlPicture
With PPT.ActivePresentation.Slides(1)
    .Shapes.PasteSpecial
    With .Shapes(.Shapes.Count)
        .Left = 200
        .Top = 100
        .Width = 500
    End With
End With
' Use this if you want to save an already existing file:
' PPT.ActivePresentation.Save
' Use this if you want to create a new file:
PPT.ActivePresentation.SaveAs ppFileName  
PPT.Quit
Set PPT = Nothing

Loop over the rows in the range A2:C12 and use Worksheets(cell1Value).Range(cell2Value).Copy where cell3Value = "Include" — Tim Williams
– Tim Williams, Commented Jun 1, 2021 at 17:19
Hi, @Tim Williams thank you for the well guidance but this is not easy for me to create the loop which works according to the Status of Col"C" if there are 5 includes then corresponding sheets range will be pasted as picture to Power Point. — user15169505
– user15169505, Commented Jun 1, 2021 at 17:29

FaneDuru · Accepted Answer · 2021-06-01 17:30:14Z

1

Please, try the next approach:

Sub SelectSheets_Ranges()
  Dim sh As Worksheet, lastR As Long, rng As Range, arr, arrSplit, i As Long, k As Long
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
  
  ReDim arr(lastR - 1)
  For i = 2 To lastR
        If sh.Range("C" & i).value = "Include" Then
            arr(k) = sh.Range("A" & i).value & "|" & sh.Range("B" & i).value: k = k + 1
        End If
  Next i
  ReDim Preserve arr(k - 1)
  For i = 0 To UBound(arr)
        arrSplit = Split(arr(i), "|")
        Set rng = Worksheets(arrSplit(0)).Range(arrSplit(1))
        Debug.Print rng.Address(external:=True): stop 'see its address in Immediate Window
        'do whatever you need with each rng...
  Next
End Sub

answered Jun 1, 2021 at 17:30

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user15169505 Over a year ago

Thank you @FaneDuru so much for this favor i would request you to please give me one more favor to add my above code to yours. If it does not bother you. Because i know to make reference for both is difficult task for me.

FaneDuru Over a year ago

@Valiant: I would do that, but I can see that you replaced your initial code with something else... Should I understand that 'Worksheets("Pivot").Range("FC3:FP35")' it the former rng to be dynamically used for different sheets?

user15169505 Over a year ago

Meanwhile I have updated the new question please fix the problem. stackoverflow.com/questions/67794292/…

Collectives™ on Stack Overflow

Adding a Loop to Code Work for Multiple Sheets

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related