Dynamic header from dependent data sheet

[kmonkmol]

I have a Data sheet their headers A1: Date, B1: Transaction Code, C1: Transaction No:, D1: Vehicle No: and E1 to P1 is My Products Name. i want in Sheet2 Daily Report My Headers is A3: Products Name, B3 to K3 is My Vehicles No: and my top of the table my criteria "Date and Transaction Name

How can i use my headers (Vehicle No: ) dynamically, because daily coming different vehicles

 

[mumps]

Where on Shee1 can I find that date's vehicle numbers . Explain in detail referring to specific cells, rows, columns and sheets using a few examples from your data.

Please answer my questions above.

 

[kmonkmol]

Please answer my questions above.

Dear mumps
My sheet1 c1 is my table header "Date" and F1 table header "Vehicle"
When i change the date in sheet2 G1, i need to get the vehicle detail's in sheet2 F4:J4

 

[mumps]

In range F4:J4 there are 5 different vehicles for Opening Stock associated with the date 19-03-2023. However, in Sheet1 there are only 3 vehicles for Opening Stock associated with 19-03-2023. The number of vehicles does not match. Please clarify in detail. Also, do you want the same thing to happen in range N4:R4 for Closing Stock?

That was my original understanding. In one of my previous responses, I posted the above questions which you yet haven't answered. Please note the underlined sections. Range F4:J4 of Sheet2 requires 5 trucks. What do you want to do if there are only 3 trucks in column F of Sheet1 that have the date of G1 of Sheet2 in column C of Sheet1. Please also answer the question in red text above.

 

[kmonkmol]

Dear mumps
My maximum number of vehicles is 5, some days truck will be 2 or 3 like, so i need when i change the date if there is only 2 or 3 i don't care but need that details, and there is no truck details need blank

 

[mumps]

Try:

Sub CopyVehicles()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS.ListObjects("Table1")
        .Range.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, desWS.Range("G1").Value)
        .Range.AutoFilter Field:=2, Criteria1:="Opening Stock"
        x = srcWS.[subtotal(103,C:C)] - 1
        If x > 1 Then
            With desWS
                .Range("F4:J4").ClearContents
                srcWS.ListObjects("Table1").AutoFilter.Range.Offset(1).Columns(4).Copy
                .Range("F4").PasteSpecial xlPasteValues, Transpose:=True
            End With
        Else
            MsgBox ("There is no data for " & desWS.Range("G1"))
        End If
        .Range.AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

If you want the same thing to happen in range N4:R4 for Closing Stock, the macro will have to be modified. Please advise.

 

[kmonkmol]

Try:

Sub CopyVehicles()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, x As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS.ListObjects("Table1")
        .Range.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, desWS.Range("G1").Value)
        .Range.AutoFilter Field:=2, Criteria1:="Opening Stock"
        x = srcWS.[subtotal(103,C:C)] - 1
        If x > 1 Then
            With desWS
                .Range("F4:J4").ClearContents
                srcWS.ListObjects("Table1").AutoFilter.Range.Offset(1).Columns(4).Copy
                .Range("F4").PasteSpecial xlPasteValues, Transpose:=True
            End With
        Else
            MsgBox ("There is no data for " & desWS.Range("G1"))
        End If
        .Range.AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

If you want the same thing to happen in range N4:R4 for Closing Stock, the macro will have to be modified. Please advise.

Hi
I'm getting error "Run time error '1004': , Autofilter method of range class failed

 

[kmonkmol]

Hi
I'm getting error "Run time error '1004': , Autofilter method of range class failed

Hi mumps
Thank you for the support, i am done in another way, i got one formula for this, i will share to you
=IFERROR(INDEX(Table1[Vehicle],SMALL(IF((Table1[Date]=$G$1)*(Table1[Transaction]="Opening Stock"),ROW(Table1[Vehicle])-ROW(Table1[#Headers])),ROW()-3)),"")
and Thanks for ChatGpt also

 

[mumps]

You are very welcome.