Excel 2007 data collection

Question

aI have an Excel 2007 workbook with about 150+ worksheets and I want to select the data from the same same cell in all worksheets and copy the data (it is all text) from only those cells that contain data; to a separate worksheet with the data listed in a column.

Answer 1

You can use the following VBA:

Dim WriteCell as Range
Set WriteCell = Sheets("New Sheet").Range("A2")

Dim MySheet as Worksheet
For Each MySheet In ThisWorkbook.Worksheets
    If MySheet.Range("B2").Value <> "" Then
        WriteCell.Value = MySheet.Range("B2").Value
        WriteCell.Offset(0, -1).Value = MySheet.Name
        Set WriteCell = WriteCell.Offset(1,0)
    End If
Next

That's if it's the same worksheet within that workbook. If you want it to be some other workbook, replace the For Each line with this:

Workbooks.Open File:= "C:\MyBook.xlsx"
For Each MySheet in ActiveWorkbook.Worksheets

This will just iterate through all of the worksheets, testing that value, and generating a worksheet with Worksheet Name and Cell Value as columns.

Answer 2

Thanks for the quick answer. I guess I'm a bit of a rookie at this, but is this a macro?? It looks like I need to substitute my file name and cell references - i.e. do I need to set this up for all the areas I want to consolidate data or does this check the entire worksheet?

When I try to run this I get a "compile error: named argument not found" for the file in the "Workbooks.Open..." line. Do I need to create that file before I run this code? Bill