Use vlookup of index match to retrieve values in multiple sheets

Nando1988

New Member
Joined
Aug 21, 2019
Messages
20
I need to make a macro, that looks for a value from data sheet, in all the sheets in the workbook, and if it's is found, it will add that value found with the next, and after all the values are found, a summary sheet will be made with the value that was searched for and the accumulated value.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hello,

Have made two assumptions:

Sheet1 is where your value from data sheet is (in Cell A1).
All the other sheets are just data entries, i.e. no formula

Code:
Sub FIND_VALUES_AND_ADD()
    MY_VALUE = Sheets("Sheet1").Range("A1").Value
        For MY_SHEETS = 2 To ActiveWorkbook.Sheets.Count
            Sheets(MY_SHEETS).Select
            Cells.SpecialCells(xlCellTypeConstants, 23).Select
            For Each MY_CELL In Selection
                If MY_CELL.Value = MY_VALUE Then
                    MY_SUM = MY_SUM + MY_VALUE
                    End If
            Next MY_CELL
        Next MY_SHEETS
        Sheets("Sheet1").Range("b1").Value = "Total Value is " & MY_SUM
        Sheets("Sheet1").Select
End Sub

is this at all close to your requirement?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top