skylarjung
New Member
- Joined
- Sep 3, 2021
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Hello,
I am looking to extract data from multiple files in a directory within the same range and put them into a "Master Worksheet". In addition I want to also insert a column right next to the previously inserted data and have the filename that the data came from.
I already have a code that I found that goes into the directory I want and extracts the data (per this forum) however I am not sure how to also insert the file name right next to the data that was inserted. Is there an easy way to do this?
I am looking to extract data from multiple files in a directory within the same range and put them into a "Master Worksheet". In addition I want to also insert a column right next to the previously inserted data and have the filename that the data came from.
I already have a code that I found that goes into the directory I want and extracts the data (per this forum) however I am not sure how to also insert the file name right next to the data that was inserted. Is there an easy way to do this?
VBA Code:
Sub ExtracData()
Dim summary As Workbook
Dim wb As Workbook
Dim directory As String
Dim fileName As String
Dim NextRow As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
directory = "C:\Users\hidden\Downloads\CRT\"
fileName = Dir(directory & "*.xl*")
Set summary = ThisWorkbook
Do While fileName <> ""
If fileName <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(directory & fileName)
wb.Worksheets("Hidden").Range("A10:A1000").Copy 'heres where it copies the range but I also need to insert the file name in the column next to it
summary.Activate
NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Worksheets("Sheet1").Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb.Close savechanges:=False
End If
fileName = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Automation Complete"
End Sub