loop through files with certain name

[daveyc18]

hi,

trying to make a macro to look through certain files like "*[ac]_greek_Analysis.xls"


then open each file....if each file has a certain value in range("c10") ...then save the file name according to that file

im currently using this code below, but it's clearly not efficient as im essentially repeating the code 11 times .... (only thing that im changing is the beginning part of file name...so instead of "1_[AC]...." ...changing it to "2_[AC]"

'first file
  Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\1_[AC]_greek_Analysis.xls")




Select Case Range("c10").Value = "12345"


Case True


 ActiveWorkbook.SaveAs Filename:= _
         ThisWorkbook.Path & "\12345.xls"


ActiveWorkbook.Close True




Case Else




    Select Case Range("c10").Value = "abcde"


Case True
 ActiveWorkbook.SaveAs Filename:= _
         ThisWorkbook.Path & "\abcde.xls"


ActiveWorkbook.Close True




Case Else


    Select Case Range("c10").Value = "456789"


Case True
 ActiveWorkbook.SaveAs Filename:= _
         ThisWorkbook.Path & "\456789.xls"


ActiveWorkbook.Close True


etc. etc.

 

[Tetra201]

Try something like this (note: not tested):

    For i = 1 To 12 Step 1
        Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\" & i & "_[AC]_greek_Analysis.xls")
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range("C10").Value & ".xls"
        ActiveWorkbook.Close True
    Next i