VBA Summary in excel

[Nando1988]

I have a table with repeated and data I need to add up from those repeated names. I have some vba code that summarizes the data from all worksheets, but only gets the first match of the name, and I need all matches in a sheet. Can someone please help. I have the names in column B, and data I need as a summary in R and AZ.
This is the code I currently have:

Sub prueba()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet, sh As Worksheet, c As Range, f As Range, j As Long
  
  Set sh1 = Sheets("Nombres Naranjo")
  Set sh2 = Sheets("Resumen Naranjo")
  Set sh3 = Sheets("Nombres Paraíso")
  Set sh4 = Sheets("Resumen Paraíso")
  sh2.Rows("2:" & Rows.Count).ClearContents
  
  j = 2
  For Each c In sh1.Range("A2:A800")
    For Each sh In Sheets
      Select Case sh.Name
        Case sh1.Name, sh2.Name
        Case Else
        If Not IsEmpty(sh1.Range("B2").Value) Then
            If sh.Name Like sh1.Range("B2").Value + "*El Naranjo" Then
                Set f = sh.Range("B2:B1048576").Find(c, , xlValues, xlWhole)
                If Not f Is Nothing Then
                    sh2.Cells(j, "A").Value = c
                    sh2.Cells(j, "B").Value = f.Offset(, 16)
                    sh2.Cells(j, "C").Value = f.Offset(, 50)
                    j = j + 1
                    End If
            End If
            If IsEmpty(sh1.Range("B2").Value) Then
                    sh2.Cells(j, "A").Value = c
                    sh2.Cells(j, "B").Value = f.Offset(, 16)
                    sh2.Cells(j, "C").Value = f.Offset(, 50)
                    j = j + 1
                    MsgBox "Entered else"
            End If
        End If
      End Select
    Next
  Next
    
  
End Sub

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...-summary-4-tables-same-sheet.html#post5350053

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).