Hello,
So basically I have 3 different spreadsheets which have a column that shows a list of names attached to open assignments. I want to create a separate tab, which I can input a users name and have a column that displays all their open assignments. I was thinking a VLOOKUP might work, but obviously that will only show me the first instance of a users name & not any subsequent instances.
I also played around w/ using a macro & VBA w/ the advanced filter. Below is what I had created so far:
Sub AdvFilterContr()
Range("B1") = Worksheets("Disc").Range("$B$1")
Sheets("Disc").Select
Range("$B$1:$T$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Worksheets("Main").Range("$B$1:$B$2"), CopyToRange:=Worksheets("Main").Range("$J$7:$AB$7"), Unique:=False
Sheets("Main").Select
Range("B16").Select
Dim i As Integer
i = 7
Do Until IsEmpty(Range("J" & i).Value)
i = i + 1
Loop
Range("B1") = Worksheets("SL").Range("$B$1")
Sheets("SL").Select
Range("$B$1:$N$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Worksheets("Main").Range("$B$1:$B$2"), CopyToRange:=Worksheets("Main").Range("$J$" & i & ":$AB$7"), Unique:=False
Sheets("Main").Select
Range("B16").Select
MsgBox i, vbInformation
End Sub
It runs the first bit of code just fine, however, when it tries to apply the filter to the second sheet, it spits back an error that I just can't seem to troubleshoot. Anyone have any suggestions or tips?
Bookmarks