Hello,
I have a piece of code which has been running and working for a few months, however, all of a sudden it will not run and I receive the runtime 1004 error as above. The de-bugger shows that the issue is the line highlighted in red.
The code goes into all files in a folder, picks up the relevant tabs and copies these into a single tab in the master file. I have looked everywhere and can't seem to find an answer to this - one idea as to add the folder location to Excel's trusted locations,
however, this has not worked.
Sub PullForecasts()
Set WBT = ThisWorkbook
Application.ScreenUpdating = False
WBT.Sheets("Forecast Master").UsedRange.Clear
Path = "C:\Users\OneDrive\Squad Tracking Folders\New Trackers"
Filename = Dir(Path & "*.xlsm")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each ws In Worksheets
If Mid(ws.Name, 1, 16) = "Updated Forecast" Then
ws.Activate
ActiveSheet.UsedRange.Select
Selection.Select
Selection.Copy
Selection.PasteSpecial xlPasteValuesAndNumberFormats
Selection.Copy
Selection.Copy Destination:=WBT.Sheets("Forecast Master").Range("A" & Rows.Count).End(xlUp)
Application.CutCopyMode = False
End If
Next ws
Workbooks(Filename).Close False
Filename = Dir()
Loop
End Sub
Any help is greatly appreciated :)
Thanks!