How to Unprotect/Copy/Paste/ClearContents without Activating the Sheets?

[m3atball]

I'm looking for a way of bringing in data from another workbook into the currently one without too much screen flickering using Excel 2007.

Currently, my code works fine with bringing the data in. But the series of actions (open the source file, select/copy data range, unprotect and clear target file's range, paste data to target, and close source file) cause differet sheets to activate back & forth. The flickering is caused by the requirement of Copy/Paste statements being stuck together. If I can do a Copy-Unprotect-ClearContents-PasteSpecial, I will only need to visit the source file once. However, since PasteSpecial only works when immediately after Copy, I have to Unprotect-ClearContents on the target sheet, go to source to copy, then come back to target again to paste.

The key question is - is it possible to do Unprotect, ClearContents, Select, Copy, PasteSpecial, and Protect all in the background without activating the sheets involved? Or, is ther any other way to get the data in without using these functions? I know my users want a tool that will work "smoothly & quietly" without seeing too many screen flashes.

Thank you in advance for your ideas!

Ideal scenario:

1. Click "import" button in ThisWorkBook's Main sheet.
2. Browse and select sourceFile, detect range for copying.
3. Unprotect "CFDS Report" sheet in ThisWorkBook, clear 4 columns, copy/paste sourceFile data, protect sheet, then close sourceFile.
All 3 steps should be done behind the scene with Main sheet being displayed the whole time.

Here is my code:

Private Sub ImportReport()

Dim i, endSourceRow As Integer
Dim temp, sourceRange, sourceFile As String

Application.DisplayAlerts = False
sourceFile = Application.GetOpenFilename("Excel files (*.xls; *.xlsx), *.xls; *.xlsx")

If InStr(sourceFile, "False") = 0 Then  'if user browses and selects a file to import

    '-----------------------------------------------------------------
    ' 0 - Clear contents of the target tabs
    '-----------------------------------------------------------------

        ThisWorkbook.Worksheets("CFDS Report").Unprotect
        ThisWorkbook.Worksheets("CFDS Report").Range("A:D").ClearContents
               
    '-----------------------------------
    ' 1 - Open user selected source file
    '-----------------------------------

    Workbooks.Open Filename:=sourceFile, AddtoMRU:=True, Editable:=True
    'Reset source file to contain just the name without path
    sourceFile = Application.ActiveWorkbook.Name
        
    '---------------------------------------------------
    ' 2 - Find out import data range from source file
    '---------------------------------------------------

    'Last line of data contains "Total:"
    i = 5
    Do While i > 0
        temp = Workbooks(sourceFile).Sheets(1).Cells(i, 1).Value
        If Trim(temp) = "Total:" Then
            Exit Do
        End If
    i = i + 1
    Loop

    'Now endSourceRow contains the bottom range for copying
    endSourceRow = i
    sourceRange = "A1:D" & CStr(endSourceRow)

    '-----------------------------------------------------------------
    ' 3 - Copy the source data range from source file
    '-----------------------------------------------------------------

     Workbooks(sourceFile).Sheets(1).Range(sourceRange).Select
    Selection.Copy
    
    '---------------------------------------------
    ' 4 - Paste the selection to the target file tab
    '---------------------------------------------
    
    ThisWorkbook.Worksheets("CFDS Report").Range(sourceRange).PasteSpecial Paste:=xlPasteAll
    ThisWorkbook.Worksheets("CFDS Report").Protect
    
    '----------------------------------------------------
    ' 5 - Close the source file and display Main tab
    '----------------------------------------------------
     Workbooks(sourceFile).Close SaveChanges:=False
    ThisWorkbook.Worksheets("Main").Activate

     Application.DisplayAlerts = True

Else

MsgBox "You didn't pick a file."

End Sub