VBA activating and screen updating

[johnmpc]

Hi,

I am using the following code to copy some data to a new workbook.
When it runs, it jumps to the new workbook even if screen updating is set to False.
I don't mind it doing that, but i want it to jump back at the end.
I have tried putting workbook.activate at the end to jump back to the correct workbook but it doesn't do anything.

Some help would be great, please. Thanks

Sub updatequotedata()

   
   Dim Ary As Variant
   Dim i As Long, j As Long, x As Long, lr As Long
   Dim c As Range
   Dim Orderline As Range
   Application.ScreenUpdating = False
   
   
   With Workbooks("John's Quote Generator Newest.xlsm").Worksheets("Order Template")
   Ary = Array("A", "D", "F", "I", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG")
   
   Workbooks.Open "https://mpcholdings.sharepoint.com/sites/salesmk/Shared Documents/John G/Quotes and Orders.xlsm"
            Set Orderline = Workbooks("Quotes and Orders.xlsm").Worksheets("Quotes").Range("A:A").Find(Workbooks("John's Quote Generator Newest.xlsm").Worksheets("Order Template").Range("A2").Value)
      
      If Not Orderline Is Nothing Then
      lastCol = Workbooks("Quotes and Orders.xlsm").Worksheets("Quotes").Cells(Orderline.Row, Columns.Count).End(xlToLeft).Column
   
   Orderline.Offset(, 1).Resize(1, lastCol - 1).ClearContents
        
         Orderline.Offset(, 0) = .Range("A2").Value
         Orderline.Offset(, 1) = .Range("F12").Value
         Orderline.Offset(, 2) = .Range("C2").Value
         Orderline.Offset(, 3) = .Range("B26").Value
         Orderline.Offset(, 4) = .Range("B4").Value
         Orderline.Offset(, 5) = .Range("B6").Value
         Orderline.Offset(, 6) = .Range("B8").Value
         Orderline.Offset(, 7) = .Range("B10").Value
         Orderline.Offset(, 8) = .Range("B12").Value
         Orderline.Offset(, 9) = .Range("B14").Value
         Orderline.Offset(, 10) = .Range("B15").Value
         Orderline.Offset(, 11) = .Range("B16").Value
         Orderline.Offset(, 12) = .Range("B17").Value
         Orderline.Offset(, 13) = .Range("B18").Value
          Orderline.Offset(, 14) = .Range("B20").Value
         Orderline.Offset(, 15) = .Range("B21").Value
         Orderline.Offset(, 16) = .Range("B22").Value
         Orderline.Offset(, 17) = .Range("B23").Value
         Orderline.Offset(, 18) = .Range("B24").Value
         Orderline.Offset(, 19) = .Range("E2").Value
         Orderline.Offset(, 20) = .Range("F2").Value
        
         x = 20
        Do While Workbooks("John's Quote Generator Newest.xlsm").Worksheets("Order Template").Range("A31:A47").Cells(M, 1).Value <> ""
               
        For i = 31 To 47 Step 1
        For j = LBound(Ary) To UBound(Ary)
         
               x = x + 1
               Orderline.Offset(, x) = .Range(Ary(j) & i).Value
               M = M + 1
        Next j
        Next i
      Loop
      
    Else
    MsgBox ("Quote not Found")
      End If
    '  Workbooks("John's Quote Generator Newest.xlsm").Worksheets("Order Template").Activate
    Application.ScreenUpdating = True
    
    Workbooks("John's Quote Generator Newest.xlsm").Activate

ThisWorkbook.Sheets("Order Template").Select

Application.ScreenUpdating = False
   End With
    
 
 
   
  
   
End Sub

 

[Fluff]

Is the code in the "John's Quote Generator Newest.xlsm" workbook?

 

[johnmpc]

Is the code in the "John's Quote Generator Newest.xlsm" workbook?

Yes

 

[Fluff]

In that case try

ThisWorkbook.Sheets("Order Template").Activate

 

[johnmpc]

In that case try

ThisWorkbook.Sheets("Order Template").Activate

Unfortunately not,
I'm not really sure why its jumping to it in the first place. Screen update is off and there doesn't appear to be anything to make it show the Quotes and Orders workbook

 

[Fluff]

When you open a workbook it will automatically be the active workbook.

Unfortunately not,

In what way, did you get any error messages?

 

[johnmpc]

When you open a workbook it will automatically be the active workbook.

In what way, did you get any error messages?

It just doesn't jump back. No error messages.

There is a possibility there is a worksheet change sub on the Quotes sheet that is changing things.

Thanks for the help. i'll do some investigating and report back