VBA focus passing

Question

trying to do in another focus with the window from the workbook from first trying to do in another focus with the window from the workbook from first

Sub Update_DHL()
    
   
    
    Workbooks.Open Filename:=trk
    Workbooks.Open Filename:=stp
    Workbooks.Open Filename:=dhl
    
    Windows(stp).Activate
    Workbooks(stpfile).Activate

    Range("B2").Select
    ActiveCell.Formula = _
    "Hi"
    Range(Cells(2, 2), Cells(2, 2)).Copy
    'Range(Cells(3, 2), Cells(65536, 45)).Select
    'Selection.ClearContents
    'Range(Cells(3, 47), Cells(65536, 74)).Select
    'Selection.ClearContents
    
   
        
'    Set wb = Workbooks("VMW Macro.xlsm")  'Name of the workbook you are copying from
'    Set ws = wb.Sheets("Extract") 'Name of sheet you are copying
'    DateStamp = Format(Now(), "mm-dd-yyyy hhmmss")



End Sub

Answer 1

Make sure you define variables for your workbooks and worksheets properly. You can then use them for your Range and Cells objects to specify in which workbook and worksheet they are. This way you don't need .Activate or .Select because the code even works if the workbook has no focus.

Make sure in your entire code there is no Range and Cells object without a workbook and worksheet specified. Either by using a variable like shown below. Or directly like ThisWorkbook.Worksheets("Sheet1").Range(…).

You only need to .Activate or .Select if you want to focus it for the user. You never need to do this to make VBA work properly (VBA can work with non-focused workbooks/worksheets).

Option Explicit

Sub Update_DHL()
    'open your workbooks
    On Error GoTo ERR_WB_OPEN
    Dim wbTrk As Workbook
    Set wbTrk = Workbooks.Open(Filename:=[truckfilePath])
    
    Dim wbStp As Workbook
    Set wbStp = Workbooks.Open(Filename:=[stopfilePath])
    
    Dim wbDhl As Workbook
    Set wbDhl = Workbooks.Open(Filename:=[dhlfilePath])
    On Error GoTo 0
    
    'define in which worksheet in those workbooks you want to work
    Dim wsTrk As Worksheet
    Set wsTrk = wbTrk.Worksheets("SheetName")
    
    Dim wsStp As Worksheet
    Set wsStp = wsStp.Worksheets("SheetName")
    
    Dim wsDhl As Worksheet
    Set wsDhl = wsDhl.Worksheets("SheetName")
    
    'now work with those worksheets directly (no activate or select needed!)
    wsStp.Range("B2").Formula = "=IF(SUMIF('Route Master.xls'!$C$7:$C$65536,$A2,'Route Master.xls'!$Q$7:$Q$65536)>0,TRUE,FALSE)"
    wsStp.Range("B2").Copy

    wsStp.Range(wsStp.Cells(2, 2), wsStp.Cells(EndRow2, 2)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ' note this code does not work because `EndRow2` is nod defined

    'select and activate a specific workbook/worksheet
    'you do this ONLY if you want to focus it for the USER. Never do this for VBA instead work directly with the worksheets as shown above.
    wbDhl.Activate
    wsDhl.Select

    Exit Sub
ERR_WB_OPEN:
    MsgBox "One of the files could not be loaded.", vbCritical
End Sub

Don't forget to close your workbooks wbDhl.Close SaveChanges:=True/False otherwise they stay open.

Answer 2

See below. You can reference the workbook directly as pointed out by BigBen. In code, you never need to select ranges or activate workbooks/worksheets. You just need to reference them directly. Notice I also added explicit declaration of types.

Dim a, b As Long

The line above will declare a as a variant and b as long

Sub Update_DHL()
    Dim trk As Workbook, stp As Workbook, dhl As Workbook, wb As Workbook, wbNew As Workbook
    Dim ws As Worksheet
    Dim stpfile As String, DateStamp As String, strFolderpath As String
    Dim EndRowTrk As Long, EndRowStp As Long, EndRowDHL As Long
    Dim fileExplorer As FileDialog
    
    Set dhl = [dhlfilePath]
    Set trk = [truckfilePath]
    Set stp = [stopfilePath]
    stpfile = stp
    
    Workbooks.Open Filename:=trk
    Workbooks.Open Filename:=stp
    Workbooks.Open Filename:=dhl
    
    With Workbooks(stpfile).Worksheets(1)
        .Range("B2").Formula = "Hi"
    End With
End Sub