Hello excel gurus,
I have a spreadsheet that copies and pastes data from excel spreadsheets within a directory. It can copy a set number of columns depending on the known files size. I have the problem, however, that the data is not labelled in any way, shape, or form. Here is the code.
Sub OpenSubfoldersFileUpdate()
Dim strFile As String
Dim objFSO, destRow As Long
Dim mainFolder, mySubFolder
Set objFSO = CreateObject("Scripting.FileSystemObject")
mFolder = Worksheets(1).Range("B12").Value
If mFolder = 0 Then
MsgBox " Address Missing"
Else: Set mainFolder = objFSO.GetFolder(mFolder)
End If
For Each mySubFolder In mainFolder.subfolders
strFile = Dir(mySubFolder & "\*.csv*")
Do While strFile <> ""
If Worksheets(1).Range("H12").Value = 0 _
Then
MsgBox "Missing Value!"
End If
If Worksheets(1).Range("H12").Value = 1 _
Then
Workbooks.Open mySubFolder & "\" & strFile
Range("A2:AT2").Copy
End If
If Worksheets(1).Range("H12").Value = 2 _
Then
Workbooks.Open mySubFolder & "\" & strFile
Range("A2:AT3").Copy
End If
If Worksheets(1).Range("H12").Value = 3 _
Then
Workbooks.Open mySubFolder & "\" & strFile
Range("A2:AT4").Copy
End If
If Worksheets(1).Range("H12").Value = 4 _
Then
Workbooks.Open mySubFolder & "\" & strFile
Range("A2:AT5").Copy
End If
If Worksheets(1).Range("H12").Value = 5 _
Then
Workbooks.Open mySubFolder & "\" & strFile
Range("A2:AT6").Copy
End If
Application.DisplayAlerts = False
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Worksheets("Sheet 1").Paste Destination:=Worksheets("Sheet 1").Cells(erow, 2)
strFile = Dir
Loop
Next
End Sub
Is there any way I can copy and paste the actual filename to the left of the data that is already being copy/pasted?
Any help you guys can give would be most appreciated.
Bookmarks