Hello! I have a VBA code which was built in 2006, worked in 2016, and broke in 2017 when we updated to Excel 2016. For the life of me, I cannot find the issue. This code is designed to link from excel file 1, copy information from excel file 2, and paste it to a worksheet in excel file 1 with some formatting. To be completely honest I am kinda desperate at this point. ANY assistance would be VASTLY appreciated...I should say the code runs 100% With no Syntax issue... However It does not actually populate the information.
sheet 1 is Invoices and Maps
sheet 2 is All Maintenance
sheet 3 is Maintenance Report
sheet 1 is Invoices and Maps
sheet 2 is All Maintenance
sheet 3 is Maintenance Report
Code:
[COLOR=#252C2F][FONT=Helvetica]Dim lst As New Collection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Sub auto_open()[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.DisplayAlerts = False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Response = MsgBox("Would you like to update list now? This will take some time.", vbYesNo)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If Response = vbYes Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]MsgBox "WARNING! Close all other excel files before proceeding!", vbCritical[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]response2 = MsgBox("All other excel files closed and ready to update?", vbYesNo)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If response2 = vbYes Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim R As Integer, rd As Long, nd As Long, amm As Long, WkbkName As Object, wkst As Worksheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim sStartPath As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim sWhat As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim result As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim t As Integer[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim tmp As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim afa As Integer[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim Rw As Integer[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim Rt As Range[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim Rmt As Range[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim stDir As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim stFile As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim z As Range[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Clear workbook in preperation for update[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each wkst In Worksheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If wkst.Name <> "Maintenance Report" And wkst.Name <> "Decayed Pole Top" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]wkst.Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]wkst.Unprotect[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]wkst.UsedRange.Delete[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next wkst[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Update Invoice Lists[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each wkst In Worksheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If wkst.Name = "Invoices and Maps" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'2017 Invoice List and hyperlink[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Be sure to correct location. Include \ at end of location.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]sStartPath = "E:\Sandbox\Test 1\Invoices" 'Where? Correct location. Enter address between quotation marks.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]sWhat = "*.xlsx" 'What?[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If lst.Count > 0 Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]lst.Remove lst.Count 'clears list if data already exists[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop Until lst.Count = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ThisWorkbook.Sheets(1).Columns(1).ClearContents[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]result = DigIn2(sStartPath, sWhat) 'First step[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For t = lst.Count To 1 Step -1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ThisWorkbook.Sheets(1).Cells(t, 1) = lst(t) 'puts data in 1st sheet, 1st column[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]lst.Remove t[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next t[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rw = Range("A65536").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]afa = 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Cell In Range("A1:A" & Rw)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & afa).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Hyperlinks.Add anchor:=Range("A" & afa), Address:=Range("A" & afa).Value[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]afa = afa + 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A:A").Sort Key1:=Range("A1"), order1:=xlAscending, Header:=xlNo, _[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'2017 Map List and Hyperlink[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Cells(1, 2).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set z = ActiveCell[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Be sure to correct location. Do not include \ at end of location.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]stDir = "E:\Sandbox\Test 1\Invoices\MAPS" 'Where? Correct location. Enter address between quotation marks. ""[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]stFile = Dir(stDir & "\*.pdf") 'What?[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do Until stFile = ""[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]z.Hyperlinks.Add z, stDir & "" & stFile, , , stFile[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set z = z.Offset(1)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]stFile = Dir()[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("B:B").Sort Key1:=Range("B1"), order1:=xlAscending, Header:=xlNo, _[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Formating Invoice and Map Sheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & 1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Selection.EntireRow.Insert[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Change to the proper year.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Range("A1") = "2017 Invoices"[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Range("B1") = "2017 Maps"[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveWindow.DisplayGridlines = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rows("1:1000").RowHeight = 20[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A:C").ColumnWidth = 20[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A:C").Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].HorizontalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].VerticalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].WrapText = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & 1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next wkst[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Update Maintenance[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Update 2017 Maintenance[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]R = Range("A65536").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Cell In Range("A2:A" & R)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next Cell[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]CopyTargetBookmark = 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Workbook In Application.Workbooks[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Correct workbook name.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If Workbook.Name <> "2017 Maintenance Invoice Log.xlsm" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Workbook.Activate[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Workbook.Worksheets(5).UsedRange.Copy[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Correct workbook name.[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Workbooks("2017 Maintenance Invoice Log.xlsm").Activate[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(2).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & CopyTargetBookmark).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Paste[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]CopyTargetBookmark = CopyTargetBookmark + Workbook.Worksheets(5).UsedRange.Rows.Count[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next Workbook[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each WkbkName In Application.Workbooks()[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Formatting[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Yearly Sheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each Worksheet In Worksheets[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If Worksheet.Name <> "Invoices and Maps" And Worksheet.Name <> "Maintenance Report" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheet.Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]rd = ActiveSheet.UsedRange.Rows.Count[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A2", "D" & rd).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Selection.Sort Key1:=Range("A2"), order1:=xlAscending, Header:=xlNo, key2:=Range("B2")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]nd = 1[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do While Cells(nd, 2).Value = Cells(1 + nd, 2).Value[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If ActiveCell.Value <> "" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rows(nd).EntireRow.Delete[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Else[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Do[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Rows("1:1").RowHeight = 30[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A:A").ColumnWidth = 12[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("B:B").ColumnWidth = 12[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("C:C").ColumnWidth = 12[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("D:D").ColumnWidth = 80[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]amm = ActiveSheet.UsedRange.Rows.Count[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A1", "D" & amm).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].HorizontalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].VerticalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].WrapText = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeLeft)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeTop)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeBottom)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeRight)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlMedium[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeLeft)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeTop)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeBottom)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlEdgeRight)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlInsideVertical)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].LineStyle = xlContinuous[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].ColorIndex = 0[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection.Borders(xlInsideHorizontal)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Weight = xlThin[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With ActiveSheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].AutoFilterMode = False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Range("A1:D1").AutoFilter[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A1", "D1").Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]With Selection[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Font.Bold = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica].Interior.Color = vbYellow[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End With[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Range("A" & 1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next Worksheet[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]'Ending[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.Range("B1").Select[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Columns("A").Hidden = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Cells.Replace What:=".pdf", Replacement:="", LookAt:=xlPart, _[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]SearchOrder:=xlByRows, MatchCase:=False[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]ActiveSheet.EnableSelection = xllockedCells[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Worksheets(1).Protect[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]MsgBox ("Update Successful!")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Else[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]MsgBox "Update Aborted! Press 'Ctrl+Shift+Q' to restart the update."[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Sub[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Else[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Sub[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.DisplayAlerts = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End Sub[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Function DigIn2(sPath As String, sWhat As String)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim fs[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim dDirs[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim dDir[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim fFile[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim c As Variant[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Dim tmp As String[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set fs = CreateObject("Scripting.FileSystemObject")[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Set dDirs = fs.GetFolder(sPath)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]For Each dDir In dDirs.SubFolders[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]tmp = DigIn2(dDir.Path, sWhat)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Next[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]tmp = Dir(dDirs.Path & "" & sWhat)[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]If tmp <> "" Then[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Do[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]lst.Add dDirs.Path & "" & tmp[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]tmp = Dir[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Loop Until tmp = ""[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]Exit Function[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#252C2F][FONT=Helvetica]End Function[/FONT][/COLOR]
Last edited by a moderator: