Sub OpenSubfoldersFileUpdate2()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("SS").Activate
Dim strFile As String
Dim objFSO, destRow As Long
Dim mainFolder, mySubFolder
Set objFSO = CreateObject("Scripting.FileSystemObject")
mFolder = Worksheets(1).Range("B14").Value
mFolder = Worksheets(1).Range("B14").Value
If mFolder = 0 Then
MsgBox "SS Address Missing"
Else: Set mainFolder = objFSO.GetFolder(mFolder)
End If
strFile = Dir(mainFolder & "\*.xls*")
Do While strFile <> ""
Workbooks.Open mainFolder & "\" & strFile
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("A2:B100").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-24
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("C3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("D2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A3").Select
ActiveSheet.Paste
Range("D3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A4").Select
ActiveSheet.Paste
Range("D4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A5").Select
ActiveSheet.Paste
Range("D5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A6").Select
ActiveSheet.Paste
Range("D6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A7").Select
ActiveSheet.Paste
Range("D7").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A8").Select
ActiveSheet.Paste
Range("D8").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A9").Select
ActiveSheet.Paste
Range("D9").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("D9").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
Range("D10").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A11").Select
ActiveSheet.Paste
Range("D11").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A12").Select
ActiveSheet.Paste
Range("D12").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A13").Select
ActiveSheet.Paste
Range("D13").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A14").Select
ActiveSheet.Paste
Range("D14").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A15").Select
ActiveSheet.Paste
Range("D15").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A16").Select
ActiveSheet.Paste
Range("F16").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A17").Select
ActiveSheet.Paste
Range("D17").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A18").Select
ActiveSheet.Paste
Range("D18").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A19").Select
ActiveSheet.Paste
Range("D19").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A20").Select
ActiveSheet.Paste
Range("D20").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A21").Select
ActiveSheet.Paste
Range("D21").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A22").Select
ActiveSheet.Paste
Range("D22").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
Range("A23").Select
ActiveSheet.Paste
Range("A1:B33").Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
erow = Sheet11.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Worksheets("SS").Paste Destination:=Worksheets("SmartScope").Cells(erow, 2)
Worksheets("SS").Range(Cells(erow, 1), _
Worksheets("SS").Cells(Rows.Count, 2).End(xlUp).Offset(, -1)).Value = strFile
strFile = Dir
Cells.Select
Cells.EntireColumn.AutoFit
Loop
ActiveWorkbook.Sheets("Input").Activate
End Sub
Any help that you can provide would be most appreciated.
Bookmarks