0

I am trying to write a short procedure that searches a database of files to find all records that match a search criteria that is listed in a column in my master workbook. I can on manage to search values in a sheet but not is directory of workbooks.

Option Explicit

Sub SearchMutipleTargets()

   Dim datasheet As Worksheet      'where is the data copied from
   Dim reportsheet As Worksheet    'where the data is pasted to
   Dim athletename As String
   Dim finalrow As Integer         'the last row of the data set 
 
   Dim i As Integer                'row counter for the loop 
   Dim t As Integer                'row count for the target names
   Dim targetcount As Integer      'how many target names there are 

   'set variables
   Set datasheet = Sheet2
   Set reportsheet = Sheet1

   'clear old data from report sheet - code clear down to row 1000
   reportsheet.Range("A9:L1000").ClearContents     

   targetcount = Cells(7, 2).End(xlUp).Row 

   'go to datasheet and start searching and copying
   datasheet.Select
   finalrow = Cells(Rows.Count, 1).End(xlUp).Row

   'loop through the target names
    For t = 1 To targetcount    
    athletename = reportsheet.Cells(t, 2).Value  

    'loop through the rows to find the matching records
    For i = 2 To finalrow
    If Cells(i, 2) = athletename Then   
        Range(Cells(i, 1), Cells(i, 12)).Copy
        reportsheet.Select
        Range("A1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        datasheet.Select
        End If
   Next i

   Next t

   reportsheet.Select  'this is so that the report sheet is selected when the procedure ends

   Range("B2").Select

   MsgBox ("Search Complete")


End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Const
  • 1
  • For that you need to loop through the dirctory to find all file names, then open each file and search in each file. But [No attempt was made](http://idownvotedbecau.se/noattempt/) to solve this so I'm not able to help you since you did not ask a quesiton according [ask]. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 17 '21 at 14:36
  • The Source: How will you identify the files to be processed? Is there a list, a folder, a file/extension pattern...? Do they all have a worksheet with the same name or the same code name (an important difference) and data in columns (`A:L`)? How to identify the criteria? Are they all strings? The Destination: Is the code located in the Destination File or in another workbook? Are the headers already written? Will you append or overwrite? How to sort the data? If you could lay out a plan with all these details covered, it may be considered as an attempt. – VBasic2008 Nov 17 '21 at 15:03
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Nov 20 '21 at 07:38

0 Answers0