Loop Macro

[zinah]

Hi,

I have a table that has dynamic range and I need to set a loop macro to generate PPTX, I have all the macros but I'm struggling with how to write the loop macro:

These are the macros:

This macro generate all employees under this manager:

Call reset_List_Leader_Organization

Dim rSht As Worksheet
Dim dSht As Worksheet
    Set dSht = Sheets("Manipulated_Data")
    Set rSht = Sheets("Role Scorecard")


Dim lastR As Long
    lastR = dSht.Cells(Rows.Count, 1).End(xlUp).Row


Dim eeid, lnm, ol, loc, supv, oLead, reg, nhrly As String
    eeid = rSht.[Z5].Value
    lnm = rSht.[Z6].Value
    ol = rSht.[Z7].Value
    loc = rSht.[Z8].Value
    supv = rSht.[Z9].Value
    oLead = "Level " & rSht.[Y8].Value & " Manager ID"


Dim eeidC, lnmC, olC, locC, supvC, oLeadC, regC, nhrlyC As Integer
    eeidC = Application.WorksheetFunction.Match(eeid, dSht.[1:1], 0)
    lnmC = Application.WorksheetFunction.Match(lnm, dSht.[1:1], 0)
    olC = Application.WorksheetFunction.Match(ol, dSht.[1:1], 0)
    locC = Application.WorksheetFunction.Match(loc, dSht.[1:1], 0)
    supvC = Application.WorksheetFunction.Match(supv, dSht.[1:1], 0)
    oLeadC = Application.WorksheetFunction.Match(oLead, dSht.[1:1], 0)
    regC = Application.WorksheetFunction.Match("Regular Employee", dSht.[1:1], 0)
    nhrlyC = Application.WorksheetFunction.Match("Ee Type", dSht.[1:1], 0)




Dim i, j As Integer
    j = rSht.[V13].Row
    
For i = 1 To lastR
    If dSht.Cells(i, regC).Value = True Then
        If UCase(dSht.Cells(i, nhrlyC).Value) = UCase("Non-Hourly") Then
            If Format(dSht.Cells(i, oLeadC).Value, "0") = Format(rSht.[W8].Value, "0") Then
                If dSht.Cells(i, olC).Value <= rSht.[Y8].Value + 2 Then
                    j = j + 1
                    rSht.Cells(j, [V13].Column).Value = dSht.Cells(i, eeidC).Value
                    rSht.Cells(j, [W13].Column).Value = dSht.Cells(i, lnmC).Value
                    rSht.Cells(j, [X13].Column).Value = dSht.Cells(i, olC).Value
                    rSht.Cells(j, [Y13].Column).Value = dSht.Cells(i, supvC).Value
                    rSht.Cells(j, [Z13].Column).Value = dSht.Cells(i, locC).Value
                End If
            End If
        End If
    End If
Next i


rSht.[U12].Value = "V12:Z" & j


Call sort_List_Leader_Organization

What I need is a loop macro that can loop into all the generated data from above macro to generate the pptx
I have Employee ID that is under drop-down list and I named the range as [ID_Layer3_4]

Sheets("Org_Layer").[ID_Layer3_4]

Once this loop finished then I can call all the macros that can generate the PPTX


Does that make sense? I'm ready to provide more details or clarify more.


Thank you!

 

[Norie]

What is it you need to loop through exactly?

 

[zinah]

I need the it to loop through below table that it's generated from the macro I shared in my first post:

V12:Z32	EeID	LAST NAME	ORG LAYER	SUPV	LOCATION
	11111	QQ	4	NNNN	123
	22222	AA	4	GGGG	125
	33333	BBB	4	CCC	127
	44444	CCC	4	HHHH	129
	555555	DDD	4	LLLLL	131
	666666	EEE	5	HHHH	133
	777777	FFF	5	LLLLL	135
	88888	HHHH	5	CCC	137
	99999	OOO	5	OKoKKK	139
	101010	LLL	5	HHHH	141

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

And the range V12:Z32 is dynamic which depends on number of generated rows that macro in post one generates.