Copy and Specific Paste based on Criteria

[rakotonirinas]

Hello everyone and happy Friday,

I would like to be able to run a report from a data sales which could be very long as it records all sales data from different fruits every month and every financial year.

The thing is not every fruit has a report for the month so sometimes, there is a gap or also the report comes in later so the chronological order is not always respected on the Sales data (as highlighted in the workbook).

Pivot can give me part of what I want but not what I really want, which is to see whether the status report of every fruit on a monthly basis (as per the Report worksheet).

I put on the Report sheet some more explanation but really what I want to achieve is that a monthly overview of sales for a selected fruit for a selected FY(financial year) and that overview always follow the start of the FY which is May and ends in April. So if there is no report then it will be a gap on the corresponding month, and if there is a value then that will be copied on the corresponding month as well. . If there is only one report, let's say only Sep-18 for one type of fruit for the whole year, then that would be displayed on the result on the 9th row of the result and not at the top of the row as it searches for the empty one.

What I thought:
- Either do vba custom sort on the range E8:G19 but I think it will be tricky
- Or add a integer j for the column but then as the FY changes, I wasn't sure how it will work.

I'm sure there is a better way for achieving this from the existing code.

Sub Report()

Dim i As Integer
Dim finalRow As Integer
Dim lngR As Long

Dim wksA As Worksheet
Dim wksB As Worksheet

Set wksB = Worksheets("Report")
Set wksA = Worksheets("Sales")

wksB.Range("E8:G19").ClearContents


finalRow = wksA.Range("B10000").End(xlUp).Row

    If wksB.Range("C4").Value = "" Then
    MsgBox ("Nothing to Search. Check Fields to Complete.")
    Exit Sub
    End If

For i = 2 To finalRow
      If wksB.Range("C4").Value <> 0 And wksA.Cells(i, 2).Value = wksB.Range("C4").Value And wksA.Cells(i, 3) = wksB.Range("C6").Value Then
          
           lngR = Application.Max(wksB.Range("E40").End(xlUp).Row, _
           wksB.Range("F40").End(xlUp).Row, _
           wksB.Range("G40").End(xlUp).Row) + 1
              
        wksA.Cells(i, 5).Copy
        wksB.Range("E" & lngR).PasteSpecial Paste:=xlPasteValues
        
        wksA.Cells(i, 7).Copy
        wksB.Range("F" & lngR).PasteSpecial Paste:=xlPasteValues
        
        wksA.Cells(i, 8).Copy
        wksB.Range("G" & lngR).PasteSpecial Paste:=xlPasteValues
        
      End If
    Next i
    
wksB.Range("C4").Select

End Sub

I really appreciate your help and thank you very much.

Rako