I have a macro to import several files in Col A:H
I would like the macro amended so as to extract the file names in Col I in the destination file for each of the files imported
Your assistance in this regard is most appreciated
Sub Open_MultipleFiles()
ChDir "C:\Extract"
Dim lr As Long
With Sheets("Imported Data")
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
Range("A2:J" & lr).ClearContents
Dim fDialog As Object, varFile As Variant
Dim nb As Workbook, tw As Workbook, ts As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.CutCopyMode = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set fDialog = Application.FileDialog(3)
ChDir "C:\extract"
With fDialog
.Filters.Clear
.Filters.Add "Excel files", "*.csv*"
.Show
For Each varFile In .SelectedItems
Set nb = Workbooks.Open(Filename:=varFile, local:=True)
With Sheets(1)
.Range("A1:H500").Copy
ThisWorkbook.Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
ThisWorkbook.Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats
End With
nb.Close False
Next
End With
With Sheets("Imported Data")
.Range("A1").EntireRow.Delete
.Range("A:H").EntireColumn.AutoFit
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.CutCopyMode = True
End With
End With
ChDir "C:\my documents"
End Sub
Bookmarks