Creating a list of data pulled from multiple worksheets

**mzbreski** · 10-20-2012, 01:22 PM

Hello,

I have searched this forum for a solution to my problem and have only been able to find a partial solution. I have a workbook with 5 worksheets. Sheets 1-4 have identical format (each sheet has patient satisfaction data for an individual clinic = 4 clinics). I would like to have sheet 5 display a list of results for questions 1,2,3, etc. The primary problem, and reason for this post, is that I have about 30 questions and over 100 clinics, so copy/paste will take ages. I found a thread on here that provided code that will create a list of data from a single cell in each worksheet (http://www.excelforum.com/excel-gene...to-a-list.html), but I will need to pull data from multiple cells per sheet. I have attached a sample file with a more thorough explanation.

The workbook that I am working with is much larger than the sample with much more data. If I know how to manipulate the code to say "Pull the data from this series of cells from each sheet and place it here", then I can use generic code to create multiple lists on the sheet called "lists".

Thanks in advance to anyone who can help me with this.

Mike

**mike7952** · 10-20-2012, 04:44 PM

Give this a try

Sub abc()
Const shList As String = "List"
 Dim ws As Worksheet
 Dim ptr As Long, icol As Long
 Dim aHeaders
 
 aHeaders = Array("Clinic", "# Responses", "Question", "Unacceptable", "Poor", "Good", "Excellent")
 With Worksheets(shList)
    .Cells.Clear
 End With
 For Each ws In Worksheets
    With ws
        If .Name <> shList Then
            icol = 1
            For ptr = 2 To .Cells(Rows.Count, "a").End(xlUp).Row Step 3
                .Range(.Cells(ptr, "a"), .Cells(ptr, "g")).Copy _
                    Worksheets(shList).Cells(Rows.Count, icol).End(xlUp).Offset(1)
                icol = icol + 8
            Next
        End If
    End With
 Next
 icol = 1
 With Worksheets(shList)
    For icol = 1 To .UsedRange.Columns.Count Step 8
        With .Cells(1, icol).Resize(, UBound(aHeaders) + 1)
            .Value = aHeaders
            .Font.Underline = xlUnderlineStyleSingle
            .Font.Bold = True
        End With
    Next
 End With
End Sub

**FDibbins** · 10-20-2012, 05:33 PM

this will do it for you with formulas.

note, i added a question numbe column to each sheet, and then added "question x" above the 2 tables

you can then just copy question 2 table for all others. (you may need to adjust ranges)

let me know if this is something you can work with?

**copead** · 10-20-2012, 05:52 PM

I've attached an alternate solution using the indirect function and a summary below. Hope this helps.

Enter these formulas in the "List" sheet:
Cell D2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),2,FALSE)
Cell E2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),3,FALSE)
Cell F2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),4,FALSE)
Cell G2: =VLOOKUP(C2,INDIRECT("'Clinic "&A2&"'!$C:$G"),5,FALSE)

You can then drag the formula down the rows to get up to 100 clinics and copy across the columns for all 30 questions. Another option would be to keep all the data in one continuous array and then use a pivot table to analyze the data. The latter approach will provide you with greater flexibility in analyzing your data.

Thanks,
Alex

**mzbreski** · 10-22-2012, 10:12 PM

Thanks everyone. I was able to make this work.

Creating a list of data pulled from multiple worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creating a list of data pulled from multiple worksheets

Re: Creating a list of data pulled from multiple worksheets

Re: Creating a list of data pulled from multiple worksheets

Re: Creating a list of data pulled from multiple worksheets

Re: Creating a list of data pulled from multiple worksheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions