Macro to Tally Survey Results from Multiple Workbooks in a Folder

Hi guys,

I'm currently conducting a survey through Excel workbooks (using drop-down lists mostly). I would like to know how to use macros to tally up the results/replies in the workbooks into one single master sheet. In other words, to use macros to extract the results/replies in the survey workbooks instead of going through each of them individually and then tallying them up. There are about 200-300 workbooks that are in my folder currently. The folder directory is: C:\Client Survey 2011. All of the workbooks located inside are listed under Client Survey 2011 (*company's name*).xlsx.

There are multiple sheets within each workbook that contains the survey questions. E.g.:

Sheet 2: D5 = Client's Name, D6 = Dealer's Name, etc
Sheet 3: E6 = Drop Down List Answer, E9 = Drop Down List Answer, D21 = Overall Comments, etc

The questionnaire itself is rather confidential, so I know it would be difficult since I can't really post any Excel sheets up. But if necessary, I could make a sample sheet of how the questionnaire is like if it helps.

Currently, I'm testing out this macro to see if it works, but I keep running into an error which says: "Subscript out of range".

Code:

Sub SurveyTest()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wkbLastRow As Double
Dim wkb As Workbook
Dim wks As Worksheet

mycount = FoundFiles

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wkb = Workbooks("C:\Client Survey 2011\Master Sheet.xlsm")
Set wks = wkb.Worksheets("Results")
Set wks = wkb.Worksheets

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\Client Survey 2011"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = _
Workbooks.Open(Filename:=.FoundFiles(lCount), _
UpdateLinks:=0)
Sheets("Your Profile").Visible = True
Range("D5").Select
ActiveCell.FormulaR1C1 = lCount

ActiveWindow.SelectedSheets.Visible = False


wkbLastRow = wks.Cells.SpecialCells(xlLastCell).Row
wks.Range("A" & wkbLastRow + 1).PasteSpecial xlPasteAll


Set wks = Nothing
Set wkb = Nothing

ActiveWorkbook.Save
ActiveWorkbook.Close

Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Although I'm not really too sure what exactly most of the code does (taken it from one of Google searches). When I debug, it returns the error right about Set wks = wkb.Worksheets(Results"). Although in the master sheet file, there is a sheet named Results, so I'm not too sure why I'm encountering this error. Hope someone here could help me. Thanks for looking into my query. Any help is much appreciated. Thanks.

What is this line of code for?

Set wks = wkb.Worksheets

I would delete this line and see how the macro runs.

But other than that, without a workbook to see what you are doing, it makes it difficult.

Hey,

Take this workbook and set it on your desktop. Put a couple of files you want the data from in a folder(Try 10 or so) and name the folder anything you like.
You have to copy the path to the folder into the Macro. The path should end with the folder name.

If you don't know how to change the path, copy the path to your folder , post it up and I will replace it for you.

Here is the workbook.

Dave

You will have to make sure that the destination is correct, but essentially , yes you are correct.
If you liked the answer please click the star in the bottom left hand corner and mark your thread closed, as per forumn rules.