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.
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
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.
Code:
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
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