Using an array? - to FIND any value on multiple sheets automatically - or any other simple way

[chazrab]

Title explains most. Code below works well as a traditional FIND method, but I can only get it to run for one sheet. How would you do this for any number
of specified sheets? My workbook has about 100 sheets. I don't want the code to go through every sheet - only ones I specify for different search values for different
sheets.

Private Sub cmdGOFIND_Click()
 Application.EnableEvents = False
 Application.ScreenUpdating = False
Sheets("REPORT").UsedRange.ClearContents
Dim lastrow As Integer
Dim X As String
Dim c As Range
Dim rw As Long
Dim firstAddress As Variant
Dim Rowno As Variant
X = Me.TextBox1.value
With Worksheets("CARDS").Range("A1:G1000")
    Set c = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
rw = 1
firstAddress = c.Address
Do
Worksheets("CARDS").Select
c.Select
Range(Cells(c.Row, 1), Cells(c.Row, 7)).copy Destination:=Sheets("REPORT").Range("A" & rw)
                rw = rw + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Else
MsgBox "No value found"
End If
End With
Rowno = Sheets("RESULT").Range("B2").End(xlDown).Row
CARDRESULTS.Show
Sheets("BUDGET").Select
Unload Me
 Application.EnableEvents = True
 Application.ScreenUpdating = True
End Sub

The question is, how would do this for multiple sheets automatically going from one sheet to the next
with sheet names specified in an array or any other way in the VB code ?.
(Sheet names are renamed CARDS2015, CARDS2016, CARDS2017, CARDS2018, CARDS2019, CARDS2020, CARDS2021, CARDS2022).
I just put the renamed sheets in to let you know that I've renamed the sheets - not the ones assigned by Excel for new sheet name designations.

Thanks for anyone's help. Seems simple enough. Just can't get the code to continue performing FIND going from sheet to sheet automatically and copying
results to a new sheet(REPORT)

cr

 

[chazrab]

Apart from using autofilter, it is a faster way of copying (or deleting) multiple ranges as you perform the operation just once - I just had one of my senior moments when first posted with the resizing of the range but glad now resolved

Appreciate additional feedback but please also mark solution used as solution as this helps others searching.

Dave

Hi Dave and thanks again. I want to make sure I'm doing this right for the benefit of others - so I just checked the check mark in the
column to the right of your and CSmith's code - a message appeared asking me to confirm this as a solution, which I did. Hope this was correct. Never did this b4, so I'm more interested in helping others which I should have done with the many solutions given to me by many various people through the years - did not realize that I should have marked all those solutions as solutions.
Thanks again, cr

 

[dmt32]

You just mark the solution used that resolved your problem - this feature is there to help others when searching forum for similar issue.

Most welcome we appreciate your feedback

Dave