0
Sub LoopAllExcelFilesInFolder()


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim i As Integer
Dim WS_Count As Integer

  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With
  myExtension = "*.xls*"
  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)

      WS_Count = ActiveWorkbook.Worksheets.Count

    For i = 4 To WS_Count
        ActiveWorkbook.Sheets(i).Select
        With ActiveWorkbook.Sheets(i)
            Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
        End With
            LastRow = RngCol.Rows.Count
            Range("L1:L" & LastRow).Value = ActiveWorkbook.Name
    Next i

      wb.Close SaveChanges:=True

      myFile = Dir

End Sub
FunThomas
  • 23,043
  • 3
  • 18
  • 34
Johnny
  • 25
  • 3
  • Please add some comments about what the problem is. Do you get some error message and at which line? – Wizhi Oct 22 '18 at 16:46
  • 2
    What is your question? Where does your code fail? – FunThomas Oct 22 '18 at 16:46
  • No errors, it just doesnt add the column to any of the workbooks. I don't know why... – Johnny Oct 22 '18 at 16:47
  • Add what column? What do you mean by "add"? And I'm going to bet it's related to your use of `.Select`/`.Activate`. It's [best to avoid using those](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BruceWayne Oct 22 '18 at 17:02
  • 1
    I can't see anywhere a statement that says "insert" or "add"..., the last part looks to me that you define the range where you want to add some values into ( `Range("L1:L" & LastRow).Value` ), but this wouldn't add any extra columns..... Maybe add something like this [How to add a new column to an existing sheet](https://stackoverflow.com/questions/7728436/how-to-add-a-new-column-to-an-existing-sheet). – Wizhi Oct 22 '18 at 17:03
  • Column L is currently blank, I'm using Range("L1:L" & LastRow).Value = ActiveWorkbook.Name to make all the cells equal the Workbook name. – Johnny Oct 22 '18 at 18:15
  • beginner mistake. somehow SaveChanges:=True got changed to False in my file. Works like a charm now. – Johnny Oct 22 '18 at 18:29

0 Answers0