title: how to create a drop down list in some column for each excel file in VBA?
body: <p>I try to create a drop down list in some column for each excel file
  after that I need to save these changes  in an excel file.</p>
  
  <pre><code>Sub convert_xls_TO_xlsx()
  
          Dim fName       As String
          Dim MyFolder    As String
  
          MyFolder = "folder\path"
       If Right$(MyFolder, 1) &lt;&gt; "\" Then MyFolder = MyFolder &amp; "\"
  
          fName = Dir(MyFolder &amp; "*.xls")
  
  
          Do While Len(fName)
               Workbooks.Open Filename:= _
               MyFolder &amp; fName
               ActiveWorkbook.Sheets("Rapport1").Select
  
               Call Macro1
  
               Name MyFolder &amp; fName As MyFolder &amp; Replace(fName, ".xls", ".xlsx", , , 1)
               fName = Dir()
          Loop
  
      End Sub
  
      Sub Macro1()
  
          lastligne = Range("B" &amp; Rows.Count).End(xlUp).Row
  
          Sheets.Add After:=Sheets(Sheets.Count)
          Range("A1").Select
          ActiveCell.FormulaR1C1 = "yes"
          Range("A2").Select
          ActiveCell.FormulaR1C1 = "No"
          Sheets("Rapport1").Select
          Range("N3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("N3:N" &amp; lastligne), Type:=xlFillDefault
  
          ActiveWindow.SmallScroll ToRight:=3
          Range("Q3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("Q3:Q" &amp; lastligne), Type:=xlFillDefault
          Range("Q3:Q8").Select
          Range("S3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("S3:S" &amp; lastligne), Type:=xlFillDefault
          Range("S3:S8").Select
          Range("U3:V3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("U3:V" &amp; lastligne), Type:=xlFillDefault
          Range("U3:V8").Select
          ActiveWindow.SmallScroll ToRight:=5
          Range("X3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("X3:X" &amp; lastligne), Type:=xlFillDefault
          Range("X3:X8").Select
          ActiveWindow.SmallScroll ToRight:=4
          Range("Z3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("Z3:Z" &amp; lastligne), Type:=xlFillDefault
          Range("Z3:Z8").Select
          ActiveWindow.SmallScroll ToRight:=3
          Range("AB3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("AB3:AB" &amp; lastligne), Type:=xlFillDefault
          Range("AB3:AB8").Select
          Range("AD3").Select
          With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=Feuil1!$A$1:$A$2"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
          Selection.AutoFill Destination:=Range("AD3:AD" &amp; lastligne), Type:=xlFillDefault
          Range("AD3:AD8").Select
          ActiveWorkbook.Save
      End Sub
  </code></pre>
  
  <p>My problem is with the creation of dropdown list in each file. How to do that please? </p>