The Problem is in the section "Intersect" nearly at the end of the code. It continuous running only when the new sheet is brought into the foreground.
Moreover, is there a way to incooperate the command "Intersect" without the "with worksheet("spread")"?
Sub Schaltfläche2_Klicken()
Dim wb As Workbook, wq As Object
Dim ws As Worksheet, datdatum
Set wb = Workbooks.Add
Set ws = wb.Worksheets("Tabelle1")
ws.Select
ActiveWindow.Zoom = 80
ws.Name = "Spread"
datdatum = Now
Set wq = Workbooks.Open
(Filename:="C:\Users\topal\Desktop\Spreaddeterminierung\Fundings 020718.xls")
wq.Sheets("Hypothekenbanken").Range("A11:P54").Copy
ws.Range("A3").PasteSpecial
wq.Sheets("Landesbanken").Range("A12:P38").Copy
ws.Range("A50").PasteSpecial
With Worksheets("Spread")
Intersect(.Range("4:92"), Range("F:F,H:H,J:J,M:M")).BorderAround , , 1
End With
End Sub
You've missed a . out from the second Range statement ...
Intersect(.Range("4:92"), Range("F:F,H:H,J:J,M:M")).BorderAround , , 1
Should be ...
Intersect(.Range("4:92"), .Range("F:F,H:H,J:J,M:M")).BorderAround , , 1
Assuming you are writing this sub in a module, Range without the . in front targets the active (foreground/frontmost) worksheet. On the other hand, if you are writing this sub in a worksheet, Range without the . in front targets the worksheet you are coding against. Thanks @Vityata for pointing this out!
In answer to your second question, you can do away with the With block by explicitly stating the worksheet in front of the .Range statments, eg ...
With Worksheets("Spread")
Intersect(.Range("4:92"), Range("F:F,H:H,J:J,M:M")).BorderAround , , 1
End With
would become
Intersect(Worksheets("Spread").Range("4:92"), Worksheets("Spread").Range("F:F,H:H,J:J,M:M")).BorderAround , , 1