Data does not completely refresh before moving to next step 1

[paulette33]

Hi -

I have an excel file that linked to several external data sources. In the code, i have a "refreshAll" command. After the sheets refresh, the pivot table is refreshed, and then the sheets are copied/saved to a new file. Everything seems to work, however, it looks like it is moving through the code before the refresh has actually completed. Is there a command that i can use that will pause the code - make the refresh complete before it moves to the next step? I pasted a copy of the code below. Thanks for your help!!!

Paulette33
********************************************************
Private Sub btnRefresh_Click()

Dim strCell As String
Dim intCol As Integer
Dim intLastCol As Integer
Dim strFileName As String

On Error GoTo btnRefreshErr

'Refresh Data Source
ActiveWorkbook.RefreshAll

'Get reporting period
strFileName = "RPT04-0140_" & Format(Cells(3, 1).Value, "yyyymm")

'Refresh Pivot Table
With Sheets("Data")
.PivotTables("CpnData").RefreshTable
.Activate
End With

'Set Column Widths on Data Sheet
strCell = ActiveSheet.Range("Results").Find("HOSP_ID").Address(False, False)
ActiveSheet.Range(strCell).Select
intCol = ActiveCell.Column
strCell = ActiveSheet.Range("Results").Find("Grand Total").Address(False, False)
ActiveSheet.Range(strCell).Select
intLastCol = ActiveCell.Column

If Columns(intCol + 1).ColumnWidth <> 14.7 Then

For x = intCol + 1 To intLastCol - 1
Columns(x).ColumnWidth = (14.7)
Next x

End If

ActiveSheet.Range("A2").Select

'Set focus back to Execute sheet
Sheets("Execute").Select

'Copy sheets to new excel workbook
With Sheets(Array("Data", "Scenario Summary", "Client Detail"))
.Copy
End With

'Set pivot table on new workbook to not automatically refresh
ActiveWorkbook.Sheets("Data").PivotTables("CpnData").PivotCache.RefreshOnFileOpen = False

'Save new workbook
ActiveWorkbook.SaveAs Filename:= _
"\\Data\Projects\Public\Bayer Coupon Report\" & strFileName & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Workbooks(strFileName).Close

Sheets("Execute").Activate

MsgBox strFileName & " has been saved", vbOKOnly


btnRefreshExit:
Exit Sub

btnRefreshErr:
If Err.Number = 1004 Then
MsgBox "File folder specified does not exist!" & Chr(13) & _
"Please save " & ActiveWorkbook.Name & _
" to the appropriate location", vbOKOnly
Else
MsgBox "An unexpected error has occured" & Chr(13) & _
Err.Number & " - " & Err.Description, vbOKOnly
End If

End Sub

 

[xlbo]

Set the backgroundRefresh property of your queries to FALSE - this will [prevent the code moving on before the refresh has taken place

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[paulette33]

Thank you for your help! It worked!!