Hi there. Hoping someone can help with this. I have set up an advance filter but as others may need to view results I need to try and automate it if possible via VBA.
I don’t really know vba so can’t do this unaided - the logic would go something like this:
I need to get a list of the data for duplicate job numbers - I can do this manually but it is a bit time consuming and I can’t pass on for others to use as main data file may not be available to them in a way they could obtain the results;
The logic would go something like this:
[What I want to do is prompt for the entry of a job number that will go in my Adanced filter]
Within VBA Open the workbook (e.g. “main file”) and then go to the worksheet (say “Dup jobs”) which is the sheet I want my advanced filter results to go in – Note: I will be filtering data from the main worksheet (say “main sheet”).
In “Dup jobs”, Clear any populated rows, but keep the column formatting if possible
Do the equivalent of going to Data/Advanced filter
Select: copy to another location
my data range in “main sheet” is A1:AI25000
my criteria range (also in “main sheet”) is AS1:CA2
copy to Dup jobs’!A1
select unique entries

The job number (8 numbers - entered at a prompt) being searched for would go in cell AX2 of "main sheet" (not sure if it would need clearing first or whether it just overwrites)?

OK to all of that, and, hopefully, my list of jobs with the heading line then appears in the “Dup jobs” worksheet in same workbook (“main file”)
Any experts out there who can help?