|Without knowing how your data is laid out, it's a little difficult to offer a solution at this point. |
It is not clear to me if there are more than one ID in Column B or multiple occurrences of each.
If there are multiple occurrences, are they sorted/grouped by ID or just randomly spread out in Column B?
You said "Pick a random Job...for one ID"
Do you mean a pick a random job for each ID - again, assuming there are multiple ID's - or do you really mean "pick a random job for just one ID?
re: Each time we run the report it should be a different Job
It is not clear to me if you want the previous reports cleared or if you are adding new random jobs to the list each time the report is run. In either case, the output from every report will have to be saved someplace and checked every time the report is run. VBA can't "remember" which rows it chose last time (or the time before that, etc.) so it would have to check the previous output(s) in order to determine if a specific job has been extracted before. The checking of the previous data will get slower and slower as the list of previous outputs grows. This may or may not be noticeable to the user, depending on the size of the data. For example, if there are 10 previous jobs already extracted, the time it takes to check the previous output won't even be noticeable. However, if there are 1000 previous outputs, the code will have to pick a random job, check it against the previous list, use it if "new" or go back and randomly pick another job, check it against the previous list, etc. The job picked might work the first time ot it might take multiple iterations to find a "new" job.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.