Find Data in Specific Cells in Mutiple Excel Worksheets using VBA

Home More Excel (VBA) Training Videos

How can I search for values in specific cells in all my Excel worksheets? I would like to search a specific cell in all my worksheets. If the value in the cell is greater than 6 then I would like to extract it

in another worksheet .Is it possible to create a button so that I can see each value in my sheets before the transfer to another sheet. Also if no values greater than 6  are found, I should see a message that 'No values greater than 6 found!'.

Would greatly appreciate your help.

Thanks!!!!!!!!! :)


So Glenn wants to find data in specific cells in multiple worksheets using VBA and finally create a report automatically. This Excel VBA code or macro can be quite helpful in a variety of situations.
We create a command button on Sheet1 and code it to select a specific cell, capture its value, assign it to a variable, select the Report Sheet, paste the value in a specific cell after finding the first empty row, go back to the next sheet and in this manner select and copy all relevant values in all the worksheets of the workbook and create a report automatically.
We also give out appropraite messages every time we find a relevant value so that the user can review it and decide to continue.
In the process we learn more interesting Excel VBA coding.

Watch the training video below to see how the complete automation solution is found using Excel VBA:

Watch this video on YouTube

Complete Excel VBA code or macro attached to the command button:

Private Sub CommandButton1_Click()
Dim ws As Worksheet, myCounter
Dim erow, myValue As Long

For Each ws In Sheets

If ws.Range("C3").Value > 6 Then

myCounter = 1

myValue = ws.Range("C3").Value


erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1) = myValue

nextValue = MsgBox("Value found in " & ws.Name & Chr(10) & _
"Continue?", vbInformation + vbYesNo, _
ws.Name & " C3 = " & ws.Range("C3").Value)

Select Case nextValue
Case Is = vbYes
Case Is = vbNo
Exit Sub
End Select
End If
Next ws

If myCounter = 0 Then
MsgBox "None of the sheets contains a " & Chr(10) & _
"value greater than 6 in cell C3 ", vbInformation, "Not Found"
End If

End Sub

Home More Excel (VBA) Training Videos