VB Code:
Set wkbSource = Workbooks.Open("C:\test\test.xls")
Set oXLSheet = wkbSource.Worksheets("Stat")
sKey = InputBox("Enter the name of the Key that u want to add", "KeyManagement")
lastRow = oXLSheet.UsedRange.End(xlDown).Row
j = lastRow + 1
oXLSheet.Cells(j, 1).Value = sKey
oXLSheet.Cells(j, 2).Formula = "=SUMPRODUCT(COUNTIF(INDIRECT(" & Chr(34) & "'Sheet1'!L2 : L1000" & Chr(34) & ")," & Chr(34) & "*" & sKey & "*" & Chr(34) & "))"
oXLSheet.Cells(j, 3).Formula= "=SUMPRODUCT(COUNTIF(INDIRECT(" & Chr(34) & "'Sheet2'!L2 : L1000" & Chr(34) & ")," & Chr(34) & "*" & sKey & "*" & Chr(34) & "))"
oXLSheet.Cells(j, 4).Formula= "=SUMPRODUCT(COUNTIF(INDIRECT(" & Chr(34) & "'Sheet3'!L2 : L1000" & Chr(34) & ")," & Chr(34) & "*" & sKey & "*" & Chr(34) & "))"