I have a template which refreshes the data (from an
external data source) on opening the template. I then
want to remove a row and do a subtotal over the data by
running some VBA code. When I step through the code, it
works fine, but at runtime, on opening the template I get
an error.
I have tried putting the data refresh into the code, but
it doesn't help.
I am new to excel programming, but this is what I've done
so far...

Private Sub Workbook_Open()

'Worksheets(1).Activate
Set currentcell = Worksheets(1).Range("A5")
Worksheets(1).Range("A5").QueryTable.Refresh

Set currentcell = Worksheets(1).Range("A5")
currentcell.EntireRow.Delete

'ActiveCell.EntireRow.Delete

'Set currentCell = Worksheets(1).Range("A5")
Range("A5").Select

Selection.Subtotal groupBy:=1, Function:=xlSum, _
totalList:=Array(7)

End Sub

The usual problem is that you are doing a background query and the code
completes before the query does. This should fix it:

Private Sub Workbook_Open()

'Worksheets(1).Activate
Set currentcell = Worksheets(1).Range("A5")
Worksheets(1).Range("A5").QueryTable.Refresh _
Backgroundquery:=False

Set currentcell = Worksheets(1).Range("A5")
currentcell.EntireRow.Delete

'ActiveCell.EntireRow.Delete

'Set currentCell = Worksheets(1).Range("A5")
Range("A5").Select

Selection.Subtotal groupBy:=1, Function:=xlSum, _
totalList:=Array(7)

End Sub

--
Regards,
Tom Ogilvy


"Cath" wrote in message
...
I have a template which refreshes the data (from an
external data source) on opening the template. I then
want to remove a row and do a subtotal over the data by
running some VBA code. When I step through the code, it
works fine, but at runtime, on opening the template I get
an error.
I have tried putting the data refresh into the code, but
it doesn't help.
I am new to excel programming, but this is what I've done
so far...

Private Sub Workbook_Open()

'Worksheets(1).Activate
Set currentcell = Worksheets(1).Range("A5")
Worksheets(1).Range("A5").QueryTable.Refresh

Set currentcell = Worksheets(1).Range("A5")
currentcell.EntireRow.Delete

'ActiveCell.EntireRow.Delete

'Set currentCell = Worksheets(1).Range("A5")
Range("A5").Select

Selection.Subtotal groupBy:=1, Function:=xlSum, _
totalList:=Array(7)

End Sub