Extract data from Multiple excel files

[rinabastia]

Dear All,

Merry Christmas & Happy New Year.

I have a folder with 100s of excel files and each excel workbook has 3 worksheets (except file names & values remaining all are same naming convention in all the excel files)

eg.,
Sheet 1= AAAAA
Sheet 2= XX Vs YY
Sheet 3= YY Vs ZZ

Here my task is I have to go to particular worksheets of the individual workbooks and get the countif (there are 4 categories ie., Major, Minor, Not Applicable & Maximum) of 6th column in the active workbook.

I have attached the template of excel file please have a look.

Regard,
RinaBastia

[SamT]

Steps in code

For each File in folder, 
with File.Sheets("CCDS Vs PI").Range("F:F") 
With Results.Sheets("Sheet1")
Results.Range("A" & NextRow) = File.Name
For each RValue in Results.Range("C2:F2)
  Results.Cells(NextRow, RValue.Column) = CountIf(File.Range("F:F:"), RValue)
Next RValue
End With Results
End With File
Next File

[rinabastia]

Dear SamT,

Extremely sorry, couldn't understand.

Regards,
Rajani