Need VBA Code

yvsriravi12047 

Sub GetUniqueValues()
    Dim ws6 As Worksheet
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
    Dim filterValue As Variant
    Dim result As Variant
    Dim i As Long, j As Long
    
    ' Set the worksheet references
    Set ws6 = ThisWorkbook.Worksheets("Sheet6")
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set ws3 = ThisWorkbook.Worksheets("Sheet3")
    Set ws4 = ThisWorkbook.Worksheets("Sheet4")
    
    ' Get the filter value from Sheet6 B3
    filterValue = ws6.Range("B3").Value
    
    ' Set the ranges based on the filter value
    If filterValue <> "" Then
        Set rng1 = ws1.Range("A:D").SpecialCells(xlCellTypeConstants)
        Set rng2 = ws2.Range("A:D").SpecialCells(xlCellTypeConstants)
        Set rng3 = ws3.Range("A:D").SpecialCells(xlCellTypeConstants)
        Set rng4 = ws4.Range("A:D").SpecialCells(xlCellTypeConstants)
    Else
        Set rng1 = ws1.Range("D:D").SpecialCells(xlCellTypeConstants)
        Set rng2 = ws2.Range("D:D").SpecialCells(xlCellTypeConstants)
        Set rng3 = ws3.Range("D:D").SpecialCells(xlCellTypeConstants)
        Set rng4 = ws4.Range("D:D").SpecialCells(xlCellTypeConstants)
    End If
    
    ' Combine the ranges and get unique values
    result = Application.WorksheetFunction.Unique(Application.WorksheetFunction.VStack(rng1, rng2, rng3, rng4))
    
    ' Sort the unique values
    result = Application.WorksheetFunction.Sort(result, 1)
    
    ' Clear existing data in Sheet6 column A
    ws6.Range("A:A").ClearContents
    
    ' Write the unique values to Sheet6 column A
    For i = 1 To UBound(result, 1)
        ws6.Cells(i, 1).Value = result(i, 1)
    Next i
End Sub

This code should work (untested) as long as you have Excel 365 or a version of Excel that supports dynamic arrays and the VStack and Unique functions. If you have an older version of Excel, you may need to use a different approach.

I hope it helps!