Fetch unique values from several sheets to one sheet.

[Lux Aeterna]

There's an excel workbook that contains several sheets with our yearly workload and I'd like to summarise that data in a new sheet.

Some of the branches we work with remain the same, while some others are added and some others leave.

List of branches are in the List sheets (List2022, List2023 etc) range K5:K38

What I'd like, if possible, is to have a copy of all branches in the Stats sheet column A, so that I can use a vlookup formula to fetch data.

Hope I made my self clear !

 

[OaklandJim]

I did not have errors here! Where is the error in the code?

I've been having issues with alternating row shading.

Try deleting this from code to see if the error is addressed:

'   -----------------------------------
'       Format Transferred Data
'   -----------------------------------

    With rTargetAnchorCell.Resize(iBranch)

        .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority

        With .FormatConditions(1).Interior
            .Pattern = xlGray16
            .PatternThemeColor = xlThemeColorDark1
            .ColorIndex = xlAutomatic
            .PatternTintAndShade = -0.349986266670736
        End With

    End With

Do you even care about alternating row shading? If so there is another way to do it.

 

[Lux Aeterna]

Do you even care about alternating row shading? If so there is another way to do it.

There's no shading at all, so I guess this is the issue. I'll try to check it tomorrow. Thanks!

 

[OaklandJim]

For the future I'd appreciate knowing what line of code causes the error?

Like I said, if you care about alternate row shading I have another way to do it. If you do not care then just delete that part of the code as shown above.

 

[Lux Aeterna]

I removed those lines from code and it worked perfectly, thanks a lot! Alternate shading is not necessary.

As for the line of code that caused the problem, I am not sure how to find it, because it didn't offer me the debug option.

 

[OaklandJim]

Thanks for the feedback. Remember to mark the solution as solved.