I have recorded and altered code in my vba program. how to repeat the same code with diff name using do while

Newton Kapildev 0
Viewed 35

This was my vba code. Kindly please help how to use do while loop to repeat the below code several times with different names. And also the below code consists of repeated codes with the process name alone modified and copy paste done. Please help in that also how to do while loop the repeated codes with different employees different processes.

I was new to vba coding. Only I am aware of recording macros and slight modifications with the help online code.

sub QPEP
Rows("1:1").Select
Selection.AutoFilter

'Need to Repeat Code From here with different Employee name

ActiveSheet.Range("$A$1:$AR$3112").AutoFilter Field:=3, Criteria1:= _
    "DEIVANAI P"
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Selection.RowHeight = 84.6
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireColumn.AutoFit
Selection.RowHeight = 85.2
Cells.EntireRow.AutoFit
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "DEIVANAI P"

'Need to Repeat Code From here with different process name

Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=8
ActiveSheet.Range("$A$1:$AR$28").AutoFilter Field:=14, Criteria1:= _
    "Bulk Tagging"
Range("au100").Value = WorksheetFunction.CountIf(Range("n:n"), "Bulk Tagging")
If Range("au100").Value = 1 Then
Range("au100").Select
ElseIf Range("au100").Value = 0 Then
Range("au100").Select
ElseIf Range("au100").Value > 1 Then
Range("av100").Select
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.Copy
Range("AV100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW100").Select
Range("AW100").Value = Range("AV100").Value / Range("AU100").Value
Range("AW100").Select
Selection.Copy
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=8
ActiveSheet.Range("$A$1:$AR$28").AutoFilter Field:=14, Criteria1:= _
    "Complex - Shelf QA"
Range("au100").Value = WorksheetFunction.CountIf(Range("n:n"), "Complex - Shelf QA")
If Range("au100").Value = 1 Then
Range("au100").Select
ElseIf Range("au100").Value = 0 Then
Range("au100").Select
ElseIf Range("au100").Value > 1 Then
Range("av100").Select
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.Copy
Range("AV100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW100").Select
Range("AW100").Value = Range("AV100").Value / Range("AU100").Value
Range("AW100").Select
Selection.Copy
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=8
ActiveSheet.Range("$A$1:$AR$28").AutoFilter Field:=14, Criteria1:= _
    "GIST Report"
Range("au100").Value = WorksheetFunction.CountIf(Range("n:n"), "GIST Report")
If Range("au100").Value = 1 Then
Range("au100").Select
ElseIf Range("au100").Value = 0 Then
Range("au100").Select
ElseIf Range("au100").Value > 1 Then
Range("av100").Select
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.Copy
Range("AV100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW100").Select
Range("AW100").Value = Range("AV100").Value / Range("AU100").Value
Range("AW100").Select
Selection.Copy
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=8
ActiveSheet.Range("$A$1:$AR$28").AutoFilter Field:=14, Criteria1:= _
    "Post Tagging"
Range("au100").Value = WorksheetFunction.CountIf(Range("n:n"), "Post Tagging")
If Range("au100").Value = 1 Then
Range("au100").Select
ElseIf Range("au100").Value = 0 Then
Range("au100").Select
ElseIf Range("au100").Value > 1 Then
Range("av100").Select
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.Copy
Range("AV100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW100").Select
Range("AW100").Value = Range("AV100").Value / Range("AU100").Value
Range("AW100").Select
Selection.Copy
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=8
ActiveSheet.Range("$A$1:$AR$28").AutoFilter Field:=14, Criteria1:= _
    "Tax Code File preparation"
Range("au100").Value = WorksheetFunction.CountIf(Range("n:n"), "Tax Code File preparation")
If Range("au100").Value = 1 Then
Range("au100").Select
ElseIf Range("au100").Value = 0 Then
Range("au100").Select
ElseIf Range("au100").Value > 1 Then
Range("av100").Select
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.Copy
Range("AV100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW100").Select
Range("AW100").Value = Range("AV100").Value / Range("AU100").Value
Range("AW100").Select
Selection.Copy
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=8
ActiveSheet.Range("$A$1:$AR$28").AutoFilter Field:=14, Criteria1:= _
    "Tax code JIRA Ticket"
Range("au100").Value = WorksheetFunction.CountIf(Range("n:n"), "Tax code JIRA Ticket")
If Range("au100").Value = 1 Then
Range("au100").Select
ElseIf Range("au100").Value = 0 Then
Range("au100").Select
ElseIf Range("au100").Value > 1 Then
Range("av100").Select
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.Copy
Range("AV100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AW100").Select
Range("AW100").Value = Range("AV100").Value / Range("AU100").Value
Range("AW100").Select
Selection.Copy
Range("af1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
Selection.AutoFilter
Columns("AF:AF").Select
Selection.NumberFormat = "0"
Range("a1").Select
End sub
vba excel
0 Answers
Related