Data table automation problem

W

Will

I've created a piece of VBA code to refresh some data tables and then
do a copy-paste special on the result... reason being is there are 16
data tables in the workbook and it runs too slowly if they are
"active".

The code I've written works the first time it is run (i.e. for one data
table), however the second time I run the code I get the following
message:

Runtime error '-2147417848 (80010108)' Automation error The object
invoked has disconnected from its clients.

The code is below, the row that is creating the error is highlighted
with ***

Sub DataTableSetup(DT)
'This macro calculates re-calculates either select or all data tables
in the model. Individual data
'tables are selected by passing the number of the data table to the
function. All data tables are selected by passing
' "ALL" to the macro. The macro paste values of data table
calculations. This is to prevent the
' model from becoming unacceptably slow when data tables exist

Dim origsheet As String
Dim origrow As Integer
Dim origcol As Integer
Dim DTRName As String ' Data table area name
Dim DTIName As String ' Data table input name
Dim DTCPName As String ' Data table copy / paste area
Dim DTHCName As String ' Data table hardcode area
Dim calcstate As String

Dim i As Integer

' Marks the orginial excel location to allow orginal location to be
returned at the end of the macro
origsheet = ActiveSheet.Name
origrow = ActiveCell.Row
origcol = ActiveCell.Column

' Sets calculation state to manual to speed up macro
calcstate = Application.Calculation
Application.Calculation = xlCalculationManual

'Test to see if 1 or all data tables require updating
If DT = "All" Then
'Calculates all data tables

'Sheets("iMacros").Select 'selects macro sheet

'For i = 1 To Range("charge_sens_data").Rows.Count 'loops over
all data tables
'This part is not yet complete
'Next i

Else
'Calculates a select data table

i = DT

Sheets("iMacros").Select 'selects macro sheet

'Retrieves required variables
DTRName = ActiveSheet.Range("DataTableAreas").Cells(i, 2).Value
DTCPName = ActiveSheet.Range("DataTableAreas").Cells(i, 3).Value
DTIName = ActiveSheet.Range("DataTableAreas").Cells(i, 4).Value
DTHCName = ActiveSheet.Range("DataTableAreas").Cells(i, 5).Value

'Selects sensitivites sheet, sets up data table and calculates
value
Sheets("oSensitivities").Select
ActiveSheet.Range(DTRName).Select

Selection.Table RowInput:=Range(DTIName) ***** PROBLEM CODE
******

Application.Calculate
ActiveSheet.Range(DTCPName).Copy
ActiveSheet.Range(DTCPName).Select

'Pastes output to iMacroSheet
ActiveSheet.Range(DTRName).Copy

'Sheets("iMacros").Select
ActiveSheet.Range(DTHCName).Select

End If

Sheets(origsheet).Select
Cells(origrow, origcol).Select
Application.Calculation = calcstate
Application.ScreenUpdating = False

End Sub


Any help would be much appreciated!

Cheers,
Will
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top