Thread: Macro that reads cell values

Hi guys,

Right the deal is I have 87 work sheets, each work sheet with a set of questions and answers exported from a questionnaire. I need to write a macro that can scan all 87 and return that 45 of them contain the answer "single" for question number 2, assuming that there are 45 out of the 87 worksheets that contain the answer "single" for question number two.

Then I need to go one step further and write one that can scan all 87 and say how many contain the answer "single" for question 2, plus contain the answer "cigarettes" for question number 7. These are hypothetical questions, I'm not actually trying to find out how many of my respondents are single and smoke cigarettes lol. But anyway, you guys seem to know what you're doing, if you want me to upload an example xls containing 10 records with full sets of answers (there are 20 questions) just let me know.

Any help however small is appreciated, thank you

~ Tom

Much simpler to write a macro that IMPORTS all 87 sheets into a single sheet that can then serve as a database for the reporting you actually DO want to create later.

So, yes, upload a couple of sample files with a sample of what you might want the merged data to layout. I have many "Consolidation" macros that would take you to that point.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Are all 87 worksheets in the same workbook, like these 10 are? I'd hate write a macro that consolidates sheets in workbook into a database, when you need sheets from OTHER workbooks consolidated.

Something like this...a new sheet called Database is added.

Each sheet would be parsed and the answers inserted into a separate column for each Questionnaire.

Once the data is collated in this manner, you have ALL the answers for any one question in a single row, or in concurrent rows for a few of them. With that, it's much simpler to construct COUNTIF() formulas against the answers in one question, or SUMPRODUCT() formulas to look for combinations of answers.

We could do it with formulas by changing the Row1 headers to match the sheet names you use, but using 100s and 100s of INDIRECT() formulas may have noticeable performance issue.

We could create a macro to run through the all the sheets and copy the answers into the format shown. As long as we know the exact cells the answers are in on each sheet, we could do that pretty simply, but it would be hardcoded.

Hopefully the code would be understandable enough you could adjust it if you decide to change your questions, answers, number of answers or location of answers.

Here's the code. It will offer to "clear" the existing database. If you say yes, all is cleared and all sheets are collated anew. If you say no, it will append new sheets only to the existing database...any sheets already in will be skipped.

So, you can add more sheets and rerun the macro anytime to

There's a button in the top left.

Code:

Option Explicit

Sub CollateDateFromSheets()
'JBeaucaire   3/11/2010
'Assemble all sheets into an organized database
Dim ws As Worksheet, wsSumm As Worksheet
Dim Col As Long, dCheck As Long

Application.ScreenUpdating = False
'Sheet to collect data into
    Set wsSumm = Sheets("Database")

'Clear existing data if desired
    If MsgBox("Clear existing summary data?" & vbLf & _
        "(No will append all new sheets to existing data)", _
            vbYesNo + vbQuestion) = vbYes Then _
                wsSumm.Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell)).Offset(0, 1).ClearContents

'column to enter next set of data
    Col = wsSumm.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
    For Each ws In Worksheets
        If ws.Name <> wsSumm.Name Then   'make sure it's not the summary sheet
            On Error Resume Next
            dCheck = Application.WorksheetFunction.Match(ws.Name, Range("1:1"), 0)
            On Error GoTo 0
            If dCheck = 0 Then           'import if sheet is new
                With wsSumm
                    .Columns(Col).ColumnWidth = 14
                    .Cells(1, Col) = ws.Name        'add title at top
                    ws.Range("B3:B7,B9,B12,B15,B18,B21,B24,B27,B30,B33,B36,B39,B42:B50,B53,B56,B59,B62,B65,B68,B71").Copy .Cells(2, Col)
                    Col = Col + 1
                End With
            Else
                dCheck = 0
            End If
        End If
    Next ws

wsSumm.Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell)).Offset(0, 1).Font.Size = 8
Application.ScreenUpdating = True
End Sub

Hah...no worries. Glad to help.

I expect you'll add a Report sheet... and in that case you'll want to make sure the macro ignores that sheet, too. Like so:

Code:

    For Each ws In Worksheets
        If ws.Name <> wsSumm.Name And ws.Name <> "Report" Then   'make sure it's not the summary sheet

...just add that in.

========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Look above, for instructions on SOLVED tags, post #13