Reporting in Excel 2010 with VBA

Question

How can I do this please!? I have an excel work book with 60 sheets and all 60 have the same column headers and number of columns.

Each sheet is the name of a country and the rows has names in column A and a status in B and a code D

I need to produce a report using all the 60 sheets that list all the Names by Country where value B = “XYV” and show the corresponding Value in D.

Hope that makes sense!

I have been trying to use a pivot table across the sheet but I either get Invalid reference if I only select the columns needed (and this takes forever to do!) or insufficient memory if I select the whole sheet.

Can I do this in VBA macro?

Sorry I'm new to Excel in this way so any help would be appreciated…

to retrieve that data you could use VLookup, or INDIRECT with MATCH formulas - take a look at Excel User MVP — Our Man in Bananas

Andy Brown Andy Brown · Accepted Answer · 2013-03-07T14:16:33

Using VBA, this works (although it needs tidying up - I've included it to give you the idea):

Sub FindItems()

Const Thing As String = "XYV"

'remember which workbook you are on
Dim wb As Workbook
Set wb = ActiveWorkbook

'create new book for answer
Dim AnswerBook As Workbook
Set AnswerBook = Workbooks.Add

'loop over sheets in original workbook
Dim ws As Worksheet
Dim c As Range
Dim BCells As Range

For Each ws In wb.Worksheets

    'check each cell in column B
    wb.Activate
    ws.Select

    Range("B1").Select
    Set BCells = Range(ActiveCell, ActiveCell.End(xlDown))

    For Each c In BCells

        If LCase(c.Value) = LCase(Thing) Then

            'add to list
            AnswerBook.Activate
            ActiveCell.Value = ws.Name
            ActiveCell.Offset(0, 1).Value = c.Value
            ActiveCell.Offset(0, 2).Value = c.Offset(0, 2).Value

            ActiveCell.Offset(1, 0).Select

        End If
    Next c

Next ws

End Sub

Reporting in Excel 2010 with VBA

1 Answers