Find Data in Specific Cells in Mutiple Excel Worksheets using VBA

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!!!!!!!!! 🙂

Glenn

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
ws.Select
ws.Range(“C3”).Select

myValue = ws.Range(“C3”).Value

Worksheets(“Report”).Select

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

One thought on “Find Data in Specific Cells in Mutiple Excel Worksheets using VBA”

  1. Sub ViewLogUp()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim lRec As Long
    Dim lRecRow As Long
    Dim lLastRec As Long
    Dim lastRow As Long
    Application.EnableEvents = False

    Set inputWks = Worksheets(“Input”)
    Set historyWks = Worksheets(“PartsData”)

    With historyWks
    lastRow = .Cells(.Rows.Count, “A”).End(xlUp).Offset(1, 0).Row – 1
    lLastRec = lastRow – 1
    End With

    With inputWks
    lRec = .Range(“CurrRec”).Value
    If lRec > 1 Then
    .Range(“CurrRec”).Value = lRec – 1
    lRec = .Range(“CurrRec”).Value
    lRecRow = lRec + 1
    .Range(“D5”).Value = historyWks.Cells(lRecRow, 3)
    .Range(“D7”).Value = historyWks.Cells(lRecRow, 4)
    .Range(“D9”).Value = historyWks.Cells(lRecRow, 5)
    End If
    End With
    Application.EnableEvents = True

    End Sub

    can you help in these codes i want to show data on my input sheet from database. when i put number in cell it should show me data from database on the cell which i want.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.