Survey results

[proctk]

Hi

don't know where to start sorry about lenght of post

I have a worksheet with 31 survey questions on it

question 1 starts on row 5.

column 1 question number
column 2 the question
column 3 first choice
column 4 second choice
colunm 5 third choice
column 5 forth choice
column 6 fith choice

When person completes the survey they select a checkbox under the culmn which represents their answer and when they are finished they click a done button on the sheet which save thier results to a folder call "viewpoint" with a random name.

The mastersheet looks the same as the surveysheet except there are no check boxes in the columns to select a answer. I'm trying to figure out a way to create a macro that will pull the results from every file in the folder "viewpoint" and add them together and place the end results in the correct column

Example option 1 option2 option3 option4 option 5

Question 1 - I like my job 5 2 3 4 5

so five people selected option 1 and 2 selected option 2 and so on

 

[erik.van.geit]

Hello,
try this

Option Explicit

Public survey_done As Boolean

Sub all_books_to_one()
'Erik Van Geit
'051105

Dim SourceBookName As String
Dim MasterBookName As String
Dim SourceRng As Range
Dim MasterRng As Range
Dim AppSetCalc As Integer

If survey_done = True Then
    If MsgBox("Already done since you opened this workbook! Proceed anyway?", 292, "ATTENTION") = vbNo Then Exit Sub
End If
survey_done = True

    With Application
    .ScreenUpdating = False
    AppSetCalc = .Calculation
    .Calculation = xlCalculationManual
    'only enable this line when you are sure no errors can occur
    'or when error handling added
    '.DisplayAlerts = False
    End With
    
    With ThisWorkbook
    ChDrive .Path
    ChDir .Path
    Set MasterRng = .Sheets("survey").Range("K3:O33")
    MasterBookName = .Name
    End With
    
    SourceBookName = Dir("*.xls")
    While SourceBookName <> ""
        If SourceBookName <> MasterBookName Then
            Application.StatusBar = SourceBookName
            Workbooks.Open FileName:=SourceBookName
            Set SourceRng = Workbooks(SourceBookName).Sheets("Survey").Range("K3:O33")
            SourceRng.Copy
            MasterRng.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd, skipblanks:=False
            Workbooks(SourceBookName).Close False
        End If
        SourceBookName = Dir
    Wend
    
    With Application
    .ScreenUpdating = True
    .Calculation = AppSetCalc
    .StatusBar = False
    '.DisplayAlerts = True
    End With

End Sub

kind regards,
Erik

 

[proctk]

Thank you for all your help

I have ben trying to get the code that you provided to work, Its not calculating the total score for all the surveys and placing the result in the correct column.

When I run the code it run through each files and I get a message, there is a large amount of information saved to the clip board, do you want to be able to past it to another program later, I have the option of Yes or no, I select yes and the code continues to run butm not thing showes up on the mastersheet

 

[erik.van.geit]

I have ben trying to get the code that you provided to work, Its not calculating the total score for all the surveys and placing the result in the correct column.

did the code "test_pastespecial_add" give you results ?
as you can imagine I tested the code whith exactly the same setup etcetera

large amount of information saved to the clip board

therefore you need to delete the quotes before ".DisplayAlerts ..."
I knew this would occur but wanted to be sure you are well aware of the consequences: if the code bugs in the middle you will have no displayalerts, unless you add some errorhandling

    'only enable this line when you are sure no errors can occur
    'or when error handling added
    '.DisplayAlerts = False

    '.DisplayAlerts = True

so main problem stays: why does the code not work for you ?
do you have the same setup ? are the files in same folder of "thisworkbook"
step through your code using function key F8 to see what happens (switch view to different workbooks where you expect events to happen)

best regards,
Erik

 

[proctk]

I got it to work, amazing, thank you for working on this with me

 

[erik.van.geit]

You're welcome
I've created an example
if somebody is intrested, please email me