Turning ScreenUpdating Off/On should not make any difference to the code whatsoever. It basically just prevents screen flicker as Excel does its calculations.
I'm not sure what you've been up to but I do see some errors in what you've been attempting. Hence, I have altered the code a little as follows:-
Dim val1 As Long: val1 = [B1]
Dim val2 As Long: val2 = [C1]
Dim lr As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Report")
Set ws1 = Worksheets("Refresh Log")
Application.ScreenUpdating = False
ws1.Range("P1", ws1.Range("P" & ws1.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
lr = ws1.Range("P" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Union(ws1.Range("B2:C" & lr), ws1.Range("P2:P" & lr)).Copy
ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
As you only need to reference two sheets, you'll notice that I have declared two worksheet variables and then set values to them. So, ws
now wholly and solely refers to the "Report" sheet and ws1
wholly and solely refers to the "Refresh Log" sheet. In order for all to work as it should, name your sheet tabs the same. I prefer to reference sheets by their sheet codes but this appears to be causing you some confusion so I think that the above method may help you to understand more clearly the whole process.
The above code now filters Column P of the "Refresh Log" sheet (ws1) for the date range that you place in cells B1(from) and C1(to) in the "Report" sheet(ws). The relevant data is then transferred from The "Refresh Log" sheet to the "Report" sheet.
The code, as you can probably tell, excludes all other sheets and will only deal with the two sheets named in the code.
Following is the link to my updated test work book:-
Play with the dates in the from and to (B1 & C1) cells in the "Report" sheet then click on "GO" to see it work.
Let me know which columns exactly you wish to extract the data from and I'll show you how to do it just in case your future data extraction range changes.
Test the above code in a copy of your work book first.
Place the above code in a separate module.