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.
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 appropriate 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:
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
If myCounter = 0 Then
MsgBox “None of the sheets contains a ” & Chr(10) & _
“value greater than 6 in cell C3 “, vbInformation, “Not Found”