data extract between sheets For the value entered

[haisathya89]

Hi team,

I don't have knowledge in VBA coding but i tired with internet search for the requirement. But unable to find the proper solution for my requirement. Hope, i get a solution from the forum.

i have attached the image of the sample data(actual data gets bigger) from the sheet named "datainvoice".I have two sheets. one is datainvoice and other is invoiceforviewing. i need to extract the data from Column O to Column V of sheet datainvoice to range B24:I41 of sheet invoiceforviewing when a enter P.Invoice no in Cell H4 of sheet invoiceforviewing

Step 1:
it has to lookup or find the occurrence of the P.Invoice no in column B of sheet datainvoice
Step 2:
Once the cell in Column B has the user entered P.Invoice no and for each each occurrence of the P.Invoice number, the data from Column O to Column V of sheet datainvoice has to be extracted and stored to array or any virtual memory.
Step 3:
the extracted data has to be sorted using the values (i.e serial number) from Column O in ascending order
Step 4:
Transfer the extracted to the range B24:I41 of sheet Invoiceforviewing without any errors

if i enter 140/23-24 in cell H4 of sheet Invoiceforviewing, the resultant should be like the below with sorted in ascending order

Hope , I explained my requirement properly. It will be great if you help me to find the solutions.

Thanks in advance.

 

[LazyBug]

Hello & welcome to the forum, @haisathya89!
Try this.
To the Sheet "invoiceforviewing" module:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
    If Not Intersect(Target, Range("H4")) Is Nothing Then
        If Not (IsEmpty(Target)) Then Call ExtractFromAnSh
        Worksheets("datainvoice").ShowAllData
    Else
        Exit Sub
    End If
End Sub

To the standart module:

Option Explicit

Sub ExtractFromAnSh()
Dim a, lr, h$, rf As Range
Application.ScreenUpdating = False

    With Sheets("datainvoice")
        .Activate
        lr = .Range("B" & .Rows.Count).End(xlUp).Row
        a = .Range(.Range("B5"), .Range("B" & lr)).Value
        Set rf = .Range("B4:V" & lr)
        On Error Resume Next
        If Not .AutoFilterMode Then rf.AutoFilter
    End With

    With Sheets("invoiceforviewing")
        .Activate
        h = .Range("H4").Value
        .Range("B24").CurrentRegion.ClearContents
        rf.AutoFilter Field:=1, Criteria1:=h
        Sheets("datainvoice").Range("B5:V" & lr).SpecialCells(xlCellTypeVisible).Copy
        .Range("B24").PasteSpecial (xlPasteValues)

        lr = .Range("B24").CurrentRegion.Rows.Count
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("D24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Range("H4").Select
    End With
       
    With ActiveWorkbook.Worksheets("invoiceforviewing").Sort
        .SetRange Range("B24:K" & 24 + lr - 1)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Application.ScreenUpdating = True
End Sub