How to keep 2nd workbook in background

[mssbass]

What's the best way to hide wkb2? This code is ending up with curser in that workbook on top. I want it to be in background (not visible to user)

Sub ImportNotes()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Set wkb2 = Workbooks.Open("\\ccx.carecentrix.com\Shares\Teams\CSC\Staffing\Confidential\MSOC\Notes_donotuse.xlsx")
ThisWorkbook.Activate
Set wsnotes = wkb2.Sheets("Sheet1")

lrnotes = wsnotes.Cells(wsnotes.Rows.Count, "A").End(xlUp).Offset(1).Row
lrsor = wssor.Cells(wssor.Rows.Count, "B").End(xlUp).Row

wssor.Range("W2:W" & lrsor).Formula = "=VLOOKUP(E2,'[Notes_donotuse.xlsx]Sheet1'!$A:$E,2,0)"
wssor.Range("X2:X" & lrsor).Formula = "=VLOOKUP(E2,'[Notes_donotuse.xlsx]Sheet1'!$A:$E,3,0)"
wssor.Range("Y2:Y" & lrsor).Formula = "=VLOOKUP(E2,'[Notes_donotuse.xlsx]Sheet1'!$A:$E,4,0)"
wssor.Range("Z2:Z" & lrsor).Formula = "=VLOOKUP(E2,'[Notes_donotuse.xlsx]Sheet1'!$A:$E,5,0)"
Application.Calculation = xlCalculationAutomatic

wssor.Range("W2:Z" & lrsor).SpecialCells(xlCellTypeVisible).Copy
wssor.Range("W2").PasteSpecial xlPasteValues

wssor.Range("Y2:Y" & lrsor).NumberFormat = "m/d/yyyy"

End Sub

 

[JLGWhiz]

ThisWorkbook.Activate

this is one of those times when 'Select' comes in handy. You can activate a workbook without it being the one in view, but if you Select the workbook, it will take screen precedence.

 

[JLGWhiz]

You would actually be better off going to the worksheet level to choose your screen image.

 

[mssbass]

I don't understand - be better off going to the worksheet level?

 

[JLGWhiz]

I don't understand - be better off going to the worksheet level?

It is the active worksheet that displays on the screen, not the workbook.

ThisWorkbook.Sheets(1).Activate

should put the first sheet of the host workbook on screen as the active window, with all other windows in the background. If you use

Windows(ThisWorkbook.Name).Activate

Then that would do the same thing. But I prefer using sheet references or variables that represent a sheet object. Excel has some peculiarities with the Selcect and Activate methods and I try to stay away from them as much as possible, but when I do use them, I try to pack it into a variable so i don't have to step down object by object.

Maybe it is splitting hairs, but it works for me.

 

[JLGWhiz]

Just an added note: If you create a new workbook by the Add method or by copying a worksheet without specifying parameters, or if you open a different workbook, then those workbooks become the active workbook and you have to reinstate the worksheet you want to be displayed, or by default the newer workbook becomes the one with focus.

 

[mssbass]

Got it thankyou

 

[JLGWhiz]

Got it thankyou

You're welcome,
regards, JLG