Auto refresh data

[rakupareek]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
    Dim cell As Range
    Dim dataSheet As Worksheet
    Dim selectedValue As String

    Set rng = Me.Range("A4:A22")
    Set dataSheet = ThisWorkbook.Sheets("Data Input Sheet")
    If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
      
For Each cell In Intersect(Target, rng)
            If cell.Value = "House Rent Allowance" Then
            Me.Range("P" & cell.Row).Value = dataSheet.Range("F14").Value
            ElseIf cell.Value = "Children Edu Allowance" Then
            Me.Range("P" & cell.Row).Value = dataSheet.Range("F18").Value
            ElseIf cell.Value = "Children Hostel Allowance" Then
            Me.Range("P" & cell.Row).Value = dataSheet.Range("F19").Value
            Else
            Me.Range("P" & cell.Row).ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If
    Exit Sub
End Sub

Using this VBA but when I change some value in Data Input Sheet then data does not refresh.
Can possible to refresh data automatic when I change any value

 

[RoryA]

Do you have formulas in A4:A22 on whichever sheet has that code in it?

 

[rakupareek]

No
There are Drop-down list

 

[RoryA]

Then I don't understand how this code is relevant to changes on the data sheet? Unless you mean you want to put a linking formula into the cells on column P instead of just the current values of F14, F18 and F19 on the data sheet so that any updates to those cells automatically flow through to the code sheet?

 

[rakupareek]

First of all I fill all required cell in code sheet A4:A22 after that I fill data in F14,18 and 19 of data sheet but the filled amount not shown in code sheet

 

[RoryA]

That's because your code inputs the value of the cells in the data sheet at the time the code runs. It sounds like you need a formula link - something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
    Dim cell As Range
    Dim dataSheet As Worksheet
    Dim selectedValue As String

    Set rng = Me.Range("A4:A22")
    Set dataSheet = ThisWorkbook.Sheets("Data Input Sheet")
    If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
      
        For Each cell In Intersect(Target, rng)
            If cell.Value = "House Rent Allowance" Then
            Me.Range("P" & cell.Row).Formula = "='" & dataSheet.Name & "'!F14"
            ElseIf cell.Value = "Children Edu Allowance" Then
            Me.Range("P" & cell.Row).Formula = "='" & dataSheet.Name & "'!F18"
            ElseIf cell.Value = "Children Hostel Allowance" Then
            Me.Range("P" & cell.Row).Formula = "='" & dataSheet.Name & "'!F19"
            Else
            Me.Range("P" & cell.Row).ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If

End Sub

 

[rakupareek]

Code working perfectly
Thanks Rory A

 

[rakupareek]

Some problem comes
When I clear content from code sheet
And reselect desired data then code not working

 

[RoryA]

"Not working" means what exactly?

 

[rakupareek]

When I clear data from column A and column P and when I re enter other data then formula not fatch from data sheet