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:
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]
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!
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:
Code:
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_OrganizationWhat 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]
Code:
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!
