Prezados,
resolvi de forma bem simples, utilizando a função CONT.SES, para a linha de kanban estática (Onde pega todos os dados independente do filtro)

=CONT.SES('Kanban Geral'!D5:D15000;"=P430A*";'Kanban Geral'!H5:H15000;"=1")*100%/C4

E para a parte filtrada, segue o VBA.

Public Sub teste()

Dim Inter, Cel
Inter = 1

For Each Cel In Sheets("Kanban geral").Range("D5:D15000").SpecialCells(xlCellTypeVisible).Cells

Sheets("Kanban Camp").Range("A15").Value = "='Kanban Geral'!" + Cel.Address

Application.Goto ActiveWorkbook.Sheets("Kanban geral").Range(Cel.Address)
 Selection.Copy
Application.Goto ActiveWorkbook.Sheets("Kanban Camp").Range("A20")
ActiveSheet.Paste

Exit For

 
Next
Call kanban



End Sub


Sub kanban()

   Rows("50:50").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Delete Shift:=xlUp

Sheets("Kanban geral").AutoFilter.Range.Copy
Application.Goto ActiveWorkbook.Sheets("Kanban Camp").Range("A50")
ActiveSheet.Paste
Rows("20:20").Select

End Sub