VBA Refresh not working

[rclangelan]

Hello all, first time poster here. I'm new to VBA and I'm having trouble with some code. I have one workbook that pulls data from another workbook when I click the refresh button. Then it counts the new data and does some processing.

Now:

It imports fine when I click the Refresh Button.

It imports fine when I run a Refresh sub by itself.

I does NOT import when I call the Refresh sub by another sub.

I tried the DoEvent code, but it is not helping.

Here is the code:


Sub CpyToTracker()


Dim recCount As Integer ' know how many to check
Dim row As Integer ' row counter for IFs
Dim nulo As Integer ' as interger
Dim freeRow As Integer ' next free row


Sheets("Import").Select ' jump to import tab

ActiveWorkbook.RefreshAll ' refresh data
DoEvents ' force update


cpyFormulas ' format all needed cells

recCount = Count_Imports ' count ODS imports via Count_Imports function
freeRow = FirstFree_KabulAst ' find first free row to paste into
nulo = 0 ' initialize new nulo count

For row = 1 To recCount ' for the total number of imported records * [cells(row, column)] *
Sheets("Import").Select ' go to Import tab
If Cells(row + 3, 3).Text = "Complete" Or Cells(row + 3, 3).Text = "#N/A" Then ' starting from C4, check status
Range(Cells(row + 3, 4), Cells(row + 3, 13)).Copy ' copy D - M
Sheets("KABUL AST").Select ' got to Kabul AST tab
Cells(freeRow, 1).Select ' go to first free row in col A in KABUL AST tab
ActiveCell.PasteSpecial (xlPasteValues) ' paste values only, not formulas
Cells(Application.ActiveCell.row, 2).Value = "Researching" ' set status to researching
Cells(Application.ActiveCell.row, 13).Value = Date ' set set import date to today
nulo = nulo + 1 ' increment new nulo counter
freeRow = freeRow + 1 ' increment to next free row
End If
Next row ' back through the loop, next row

Sheets("KABUL AST").Select ' go to KABUL AST tab
Sort ' sort by status
Range("A8").Select ' jump to first sorted nulo
MsgBox "You've imported " & recCount & " NULOs from ODS and " & nulo & " are new to your tracker." ' output new nulos to msgbox

End Sub

I know it's not the most efficient code, but it works... all except the refresh. If I break the process and end the run, it does show that it imported, but not if I let the process finish.

Thoughts? Thanks,

Ryan

 

[alvechurchdata]

This reads like Excel VBA rather than Access. You might get a better response from the Excel forum.

Geoff Franklin

http://www.alvechurchdata.co.uk