I have 6 excel worksheets that are connected to a SQL database. I have a macro that refreshes all worksheets with the correct data from the database. The problem is that when i click on the RUnRefresh macro it gives and error 1004. "Please wait while excel refreshes then rerun again". so when that error comes it always goes to worksheet 6 and i have to be pressing F5 so that it refreshes worksheets 7, 8, 9 and so on until it fininshes . Here is the code below, someone please help

Select Code copy to clipboard

Code:
Private Sub UserForm_Activate()
refreshData
copyCY
copyPY
Me.Hide
End Sub

Public Sub refreshData()

Dim r As Integer
Dim i As Integer
Dim k As Integer
Dim h As Integer
Dim s As String

Sheet4.PivotTables(1).RefreshTable
Sheet5.PivotTables(1).RefreshTable
Sheet6.PivotTables(1).RefreshTable
Sheet7.PivotTables(1).RefreshTable
Sheet8.PivotTables(1).RefreshTable
Sheet9.PivotTables(1).RefreshTable



End Sub
Code:
Private Sub copyCY()
Dim r As Integer
Dim i As Integer
Dim c As Integer
Dim h As Integer

For i = 1 To Sheet6.Cells.SpecialCells(xlLastCell).Row Step 1
        If Sheet6.Cells(i, 1) = "Grand Total" Then
         r = i
         Exit For
         End If
Next i

For i = 1 To Sheet6.Cells.SpecialCells(xlLastCell).Column Step 1
        If Sheet6.Cells(i, 1) = "Grand Total" Then
         c = i
         Exit For
         End If
Next i
Sheet10.Cells(4, 2) = Year(Sheet6.Cells(4, 2))
Sheet10.Cells(4, 3) = Year(Sheet7.Cells(4, 2))
Sheet10.Cells(4, 7) = Year(Sheet6.Cells(4, 2))
Sheet10.Cells(4, 8) = Year(Sheet7.Cells(4, 2))
Sheet10.Cells(4, 12) = Year(Sheet6.Cells(4, 2))
Sheet10.Cells(4, 13) = Year(Sheet7.Cells(4, 2))

For i = 1 To 53 Step 1
    If IsDate(Sheet6.Cells(4, i * 2)) = True Then
        Sheet10.Cells(i + 4, 1) = Sheet6.Cells(4, i * 2)
        Sheet10.Cells(i + 4, 2) = Sheet6.Cells(r, 1 + i * 2)
        Sheet10.Cells(i + 4, 6) = Sheet6.Cells(4, i * 2)
        Sheet10.Cells(i + 4, 7) = Sheet6.Cells(r, i * 2)
        Sheet10.Cells(i + 4, 11) = Sheet6.Cells(4, i * 2)
        
        If Sheet10.Cells(i + 4, 7) > 0 Then
            Sheet10.Cells(i + 4, 12) = Sheet10.Cells(i + 4, 2) / Sheet10.Cells(i + 4, 7)
        Else
            Sheet10.Cells(i + 4, 12) = ""
        End If