Tricky Vba to extract few data with condition and few randomly

Question

I have a file with one sheet named as Audit file. It has multiple columns until column Z

The first column(A) has auditors name column (C) has regions and column (X) has decisions(Valid, invalid, correct incorrect and dropdown lists)

First row has headers.

Each person works on multiple regions,

I need to extract 27 rows for each person's each region and paste it in a new sheet and name it Verification (example: person A works on UK, US, IND,etc, similarly person B works on UK, SD, IS, etc, The code should extract for person A region UK 27 rows, For person A US region 27 rows, Person A IND region 27 rows, then move to the next person B UK region @7 rows and so on....)

In 27 rows for each persons each region, all the rows containing Valid in Column X must be included and the remaining rows can be randomly selected excluding the Valid values in column X.

How I do Manually is, first filter person A, then filter Region UK then Filter Valid decision, copy everything and paste in sheet Verification, the remove the filter and select the remaining rows randomly and paste them in Verifivation sheet, then I do it for next region, once Person A's regions are completed I move to Person B, and so on.

Option Explicit
Public gcolNames As Collection
Public Const kiPULLQTY = 27
Public Const kiColREG = 3
Public giMarker As Long
Public rng As Range
Public Sub RunData()
LoadNames
MakeResults
End Sub

Private Sub AuditList()
Dim iRows As Long
On Error Resume Next
   
    Sheets("Results").Delete
    Sheets("Auditors").Delete
    Sheets("Data").Select
    'Sheets.Add
    'ActiveSheet.Name = "results"
   
    Sheets.Add
    ActiveSheet.Name = "auditors"
   
    Sheets("Data").Select
    Columns("A:A").Select
    Selection.Copy
    Sheets("auditors").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
   
    iRows = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Range("$A$1:$A$" & iRows).RemoveDuplicates Columns:=1, Header:=xlYes
   
    LoadNames
    End Sub
    'load the auditors
    Private Sub LoadNames()
    Dim sName As String
   
    On Error Resume Next
    Set gcolNames = New Collection
   
    Sheets("auditors").Select
    Range("A2").Select
    While ActiveCell.Value <> ""
       sName = ActiveCell.Value
       gcolNames.Add sName, sName
       
       ActiveCell.Offset(1, 0).Select 'next row
    Wend
   
    Sheets("Data").Select
    Range("A1").Select
    End Sub
    Private Sub MakeResults()
    Dim i As Integer
    Dim vName

    Sheets("Data").Select
    Range("A1").Select
    For i = 1 To gcolNames.Count
    vName = gcolNames(i)

    Set rng = ActiveSheet.UsedRange
    Selection.AutoFilter
    rng.AutoFilter Field:=1, Criteria1:=vName
    GoSub PostResults
   
    Sheets("Data").Select
    Selection.AutoFilter  'filter off
   
    Next
    Sheets("Data").Select
    Selection.AutoFilter
    Exit Sub

    Set rng = ActiveSheet.UsedRange
    rng.Copy
   
    Sheets.Add
    ActiveSheet.Name = vName
    Sheets(vName).Activate
    ActiveCell.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
   
    Return
    End Sub

I have this code gathered from online sources, but it doesn't work the code only paste all the values in seperate sheet based on auditor names. it doesn't extract 27 columns even for each person.

You will benefit greatly from reading on [how to avoid using select](https://stackoverflow.com/a/10717999/15597936). Select/Activate is generally a bad practice. — Raymond Wu, Nov 02 '21 at 05:58
Im sorry have now edited it properly, please help me with this code — N S, Nov 02 '21 at 06:26
@braX I have edited the problem, can u please help me with this code. — N S, Nov 02 '21 at 06:27
total 27 rows, in which All valid must be present remaining must be random Total 27 rows= valid + random rows, so Random rows= 27-valid decisions in column x — N S, Nov 02 '21 at 06:38
@norie total 27 rows, in which All valid must be present remaining must be random Total 27 rows= valid + random rows, so Random rows= 27-valid decisions in column x — N S, Nov 02 '21 at 08:49

Tricky Vba to extract few data with condition and few randomly

0 Answers0