VBA - not updating target sheet

[davidepstein22]

Hello,

Step 1 - I have a power query setup that automatically loads data into my spreadsheet and it runs successfully.
Step 2 - The code determines if the row being processed (from the PQ sheet) is a new row or updating an existing row. It inserts new rows successfully. However, the updates are not working unless I run the update 2-times in a row, which is really odd. Furthermore, if I step through the the code in debug mode it works fine. It is almost as if the code is running too fast, which I don't believe, and the sheet is not updating.

I spent far too many hours trying all different variations, but no luck... I would really appreciate some guidance.

I bolded the code that is not working (see below).

Sub ImportShippingData()
'Declare Variables

Dim myWorkbook As Workbook
Dim found As Range
Dim lookupvalue, myWB As String
Dim addedRow As ListRow
Dim lastrow As Integer

Application.ScreenUpdating = False
ThisWorkbook.RefreshAll
DoEvents

On Error Resume Next
'clear filters from RBO
ThisWorkbook.Worksheets("RBO").Activate
ThisWorkbook.Worksheets("RBO").ShowAllData

'clear filters from Received
ThisWorkbook.Worksheets("Received").Activate
ThisWorkbook.Worksheets("Received").ShowAllData
On Error GoTo 0
DoEvents

ThisWorkbook.Worksheets("RBO").Activate
ThisWorkbook.Worksheets("RBO").Range("J2").Select ' column J is the Concatenated field

Do While ActiveCell.Text <> ""
'select first value to lookup from Received sheet
lookupvalue = ActiveCell.Text

ThisWorkbook.Worksheets("Received").Activate

Set found = Worksheets("Received").Cells.Find(What:=lookupvalue, After:= _
Range("j1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

With Worksheets("Received")

If found Is Nothing Then 'insert row into Received sheet

Set addedRow = .ListObjects("Received").ListRows.Add
ThisWorkbook.Worksheets("Received").Range("J" & ThisWorkbook.Worksheets("Received").ListObjects("Received").Range.Rows.Count).Select

ThisWorkbook.Worksheets("RBO").Activate
With addedRow
.Range(1) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -9)
.Range(2) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -8)
.Range(3) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -7)
.Range(4) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -6)
.Range(5) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -5)
.Range(6) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -4)
.Range(7) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -3)
.Range(8) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -2)
.Range(9) = ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -1)

End With

Set found = Nothing

Else 'update row
foundrow = found.Row

ThisWorkbook.Worksheets("RBO").Activate
company = Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -9)
qty = Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -4)
warehouse = Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -3)
etd = Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -2)
eta = Worksheets("RBO").Range(ActiveCell.Address).Offset(0, -1)

ThisWorkbook.Worksheets("Received").Activate
Worksheets("Received").Range("A" & foundrow).Value = company
Worksheets("Received").Range("F" & foundrow).Value = qty
Worksheets("Received").Range("G" & foundrow).Value = warehouse
Worksheets("Received").Range("H" & foundrow).Value = etd
Worksheets("Received").Range("I" & foundrow).Value = eta
ThisWorkbook.Worksheets("RBO").Activate

Set found = Nothing
End If
End With

DoEvents

ThisWorkbook.Worksheets("RBO").Activate
ThisWorkbook.Worksheets("RBO").Range(ActiveCell.Address).Offset(1, 0).Select

Loop

ThisWorkbook.Worksheets("Summary").Activate
ThisWorkbook.Worksheets("Summary").Range("A1").Select
Application.ScreenUpdating = True

'display ending message
MsgBox "Update Complete"

End Sub

 

[Alex Blakenburg]

In Power Query settings try unchecking enable background refresh.

 

[davidepstein22]

In Power Query settings try unchecking enable background refresh.

Thanks Alex - that WORKED!!! Why was that the issue? I want to understand.... Really happy

 

[Alex Blakenburg]

I got it from Celia Alves. It is a common issue when you load Power Query to a table and then have a pivot on top op that table.
You want one refresh to run "after" the other one finishes and background refresh let's them run in parallel which you don't want it to do.