VBA Code to Copy table contents as values to another sheet works only when have a Breakpoint

[AEngineer]

I have some code that copies the contents of a table on one sheet and pastes the values into another sheet. This is part of a longer process that opens an external workbook, copies its contents into a tab in the main workbook, the updates a table via refreshing a query. This part all works fine. Where I run into trouble is when I try to copy that refreshed table and then paste its values into another sheet.

• If I run with no breakpoint the updated table does not copy (but if I run again it does)
• If I put a breakpoint at the call to the sub that makes the copy and then resume it copies well.

What I've Tried that did NOT help

• Starting running the code on a different sheet - shouldn't make a difference, and didn't
• Inserting a Pause in the execution via a wait of up to 2min
• Replacing the sub that doesn't work right with the two calls it contains
• Putting the breakpoint at other places in the code

My general question is: What does putting a breakpoint in execution of code do that might cause this behavior?

FWIW here's the code section - I've put module variable definition and initiation in their own module that I'm not showing.

Master sub
Sub MasterCashFlow()
' Update 3/11/2022 - Works through creating subtotals
' This calls all the routines to copy from Smartsheet Output to creating subtotals
' 3/11/2022 still missing refreshing Queries

' First, Turn off display
'Description:
Application.ScreenUpdating = False
Application.Calculation = xlManual ' 3/22/2022 turned off to see if affects copying. Didn't
Application.DisplayAlerts = False
Application.EnableEvents = False

' Now do the work
Call SetPublicVariables ' CF_01 First - Set Public variables
Call CopyDataFromWorkbookv2 ' CF_02 Now get data
Workbooks(SourceFileName).Close ' Close the source file 3/22/2022 moved location
ThisWorkbook.RefreshAll ' Refresh the queries - takes about 30 secs
' 3/22/2022 works right to here

' Application.Wait (Now() + TimeValue("00:00:05")) ' 3/22/2022 added to see if pausing solves problem

' This is the Call that works fine (meaning copies as intended) if have breakpoint
Call CopyAsCall ' CF_03 Copies to clean Subtotals sheet
' Replacing Copy as Call with its routines doesn't solve problem
' Call ClearRegionSubtotals ' Clears subtotals sheet
' Call CopyFirstListObject ' Copies data from output Table in another tab

' 3/22/2022 The following don't have
Call SS_Subtotals 'CF_04 Creates Subtotals
Call UpdateGT ' Cleans up subtotals sheet and will format it ultimately
Call Fmt_Subt_Mstr ' Will update column widths and dollar formats

' Workbooks(SourceFileName).Close ' Close the source file - moved earlier 3/22/2022


' Turn display and calculation back on
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True

MsgBox "The Subtotals and CashFlow are Updated."

End Sub

Here are the relevant subs

Sub CopyAsCall()
' 3/9/2022 Testing
' uses subs via call to copy from output to subtotals

Call ClearRegionSubtotals ' Clears subtotals
Call CopyFirstListObject ' Copies data from output Table

' Initialize

' Worksheets(wksSubtotalsName).Activate
' Range(CellSubtotalsStartName).Select ' put focus on where want to paste
' Range(CellSubtotalsStartName).PasteSpecial Paste:=xlPasteValues ' 3/22/2022 moved to copyFirstListObject

' Call SS_Subtotals ' Will put this as part of master.

End Sub



Sub CopyFirstListObject()
' 3/9/2022 Works well to copy the output of Cashflow, ready to paste
' to display the listobjects
' Soucre ListObjects.Item property (Excel)
' Reminder - Must initialize with SetPublicVariables


Call SetPublicVariables ' Safety to be sure have them initialized
Dim wrksht As Object
Dim oListObj As String

Worksheets(wksQueryOutputName).Activate ' Be sure are working with output sheet
Set wrksht = ActiveWorkbook.Worksheets(wksQueryOutputName) ' create object

oListObj = wrksht.ListObjects.Item(1).Name

ActiveSheet.ListObjects(oListObj).Range.Copy ' This works

Workbooks(wbStart).Worksheets(wksSubtotalsName).Activate
' Range(CellSubtotalsStartName).Select ' 3/22/2022 added to see if fixed copy problem


Range(CellSubtotalsStartName).PasteSpecial Paste:=xlPasteValues ' 3/22/2022 from CopyAsCall

End Sub

Sub ClearRegionSubtotals()
' 3/22/2022 Still works
' This is intended to reset the subtotals region
' Assumes have target cell in upper left

' Dim CellStartName As String ' it's now public

' Initialize
' Call SetPublicVariables ' Safety to be sure have them initialized

Worksheets(wksSubtotalsName).Activate ' Be sure are working with output sheet

Range(CellSubtotalsStartName).Activate ' Be sure are starting on Subtotals

On Error Resume Next ' Should bypass delisting table if not a table
ActiveSheet.ListObjects(1).Unlist ' turns from table to range - assumes is table

Range(CellSubtotalsStartName).CurrentRegion.Select ' works
Range(CellSubtotalsStartName).CurrentRegion.RemoveSubtotal 'Works
Range(CellSubtotalsStartName).CurrentRegion.Clear 'Works

Range(CellSubtotalsStartName).Select

End Sub

 

[GWteB]

This kind of behavior is sometimes a sign that other (dependent) processes are not getting enough attention. Often this can be solved by yielding to the OS.
This can be done using the VBA.DoEvents function. See whether this helps.

 

[AEngineer]

I tried this by adding a DoEvents in two places where I thought it make a difference as shown in the code below. Unfortunately, the problem persists. Without breakpoint the table values that copy are from the just-prior version of the table. With the breakpoint, the copied values are correct.

Any other ideas?

....
Workbooks(SourceFileName).Close ' Close the source file 3/22/2022 moved location
DoEvents ' 2022-03-23 Added on recommendation of MrExcel Forum
ThisWorkbook.RefreshAll ' Refresh the queries - takes about 30 secs
DoEvents ' 2022-03-23 Added on recommendation of MrExcel Forum
' 3/22/2022 works right to here

' Application.Wait (Now() + TimeValue("00:00:05")) ' 3/22/2022 added to see if pausing solves problem

' This is the Call that works fine (meaning copies as intended)if have breakpoint
Call CopyAsCall ' CF_03 Copies to clean Subtotals sheet
' Replacing Copy as Call with its routines doesn't solve problem
....

 

[AEngineer]

I tried this by adding a DoEvents in two places where I thought it make a difference as shown in the code below. Unfortunately, the problem persists. Without breakpoint the table values that copy are from the just-prior version of the table. With the breakpoint, the copied values are correct.

Any other ideas?

....
Workbooks(SourceFileName).Close ' Close the source file 3/22/2022 moved location
DoEvents ' 2022-03-23 Added on recommendation of MrExcel Forum
ThisWorkbook.RefreshAll ' Refresh the queries - takes about 30 secs
DoEvents ' 2022-03-23 Added on recommendation of MrExcel Forum
' 3/22/2022 works right to here

' Application.Wait (Now() + TimeValue("00:00:05")) ' 3/22/2022 added to see if pausing solves problem

' This is the Call that works fine (meaning copies as intended)if have breakpoint
Call CopyAsCall ' CF_03 Copies to clean Subtotals sheet
' Replacing Copy as Call with its routines doesn't solve problem
....

I'm suspicious that there's something causing problems due to RefreshAll, but cannot be more specific.

 

[GWteB]

Pity it didn't resolve your issue. Wouldn't know what else might be causing your problem. Hopefully other forum members have an idea of what could be going on here.