Sorry about not providing details
I have a workbook called Interface.xls and another workbook called
IRReports.xls
When I enter a date (format dd-mmm-yyyy hh:mm) in cell G4 on worksheet
"TOC" in Interface.xls, the Private Sub Worksheet_Change(ByVal Target
As Range) subroutine for the "TOC" worksheet is triggered. There are
line of code in this subroutine that call the Update_Downtime
subroutine. One example looks like this:

Call Update_Downtime("IRReports.xls", "PIR-DT DAY", "B3", "C3",
"=PICompDat($B$4,$E$1,$E$2+1/24,9,""osi"",""inside"")", "A", "B", "C",
"K")

"IRReports.xls" is as I stated the name of the other worksheet

"PIR-DT DAY" is the name of the worksheet in IRReports.xls

"B3" has a text value which represents a range of cells, so for example
the value in B3 in PIR-DT DAY worksheet would be "A5:B7"

"C3" also has a text value which represents a range of cells, so for
example the value in C3 in PIR-DT DAY worksheet would be "C5:K7"

"=PICompDat..." is an add-in function to calculate something

And the "A", "B", "C", "K" variables are just to identify columns

The whole point of the Update_Downtime subroutine is to clear previous
data in rows by using the LastRow functions and knowing where the first
row of data always starts

After clearing the data, we have to update the data using the new date
that was entered in Interface.xls

I have included below the Update_Downtime subroutine with more
comments

Public Sub Update_Downtime(WkBookName As String, WkSheetName As String,
ArrayRange As String, Range2 As String, _
PICompDatFormula As String, Col1 As String,
Col2 As String, Col3 As String, Col4 As String)

Dim myexcel As Object
Dim myworkbook As Object
Dim myworksheet As Object
Dim LastRow As Long
Dim RangeToClear As String
Dim IRArray As String

'Turn off screen updating while macro runs
Application.ScreenUpdating = False

'Point to relevant Excel objects
Set myexcel = GetObject(, "Excel.Application") 'Point to active
excel application
Set myworkbook = Excel.Application.Workbooks(WkBookName) 'Point to
the relevant workbook
Set myworksheet = myworkbook.Worksheets(WkSheetName) 'Point to the
relevant worksheet

'Check for last Row used in the downtime summary worksheet
LastRow = myworksheet.Cells.Find(What:="*",
After:=myworksheet.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
'If Last Row used is greater than 4, then clear formula array in the
rows greater than 4 using the Col1 and Col2
If LastRow 4 Then
'Clear the PIComp data array range
RangeToClear = Col1 & "5:" & Col2 & LastRow
myworksheet.Range(RangeToClear).Select

Selection.ClearContents

'Clear the other PI data range except the first row
RangeToClear = Col3 & "6:" & Col4 & LastRow
myworksheet.Range(RangeToClear).SelectSelection.Cl earContents
End If

'Fill the PICompDat data with an array formula
MsgBox "select the first cell for array formula"
'Check if there is any PI data for the date range
If myworksheet.Range(ArrayRange).Value < "None" Then
'Select the range of cells to enter the PI data
IRArray = myworksheet.Range(ArrayRange).Value
myworksheet.Range(IRArray).Select 'Fill in the array formula for
the PI data
Selection.FormulaArray = PICompDatFormula

'Fill the adjacent columns with PI data
'Select the range of cells to fill down all the other PI info in
adjacent columns
IRArray = myworksheet.Range(Range2).Value
myworksheet.Range(IRArray).Select Selection.FillDown
End If

'Turn back on screen updating after macro runs
Application.ScreenUpdating = True

End Sub

The problem is that at the first sign of selecting a range of cells to
clear in the PIR-DT DAY worksheet (see in red above), I get the error
stated previosuly
The problem is that PIR-DT DAy is not supposed to be visible to the
user...it is only supposed to b eused to do some calculations in the
background
When IRReports.xls is opened, it will always and should always open on
the "DYREPMST" worksheet...hence lies the problem
If the DYREPMST worksheet is the active sheet, aparently there is no
way to do the macro in the abckground without having to actually select
the PIR-DT DAY worksheet


--
suzetter
------------------------------------------------------------------------
suzetter's Profile: http://www.excelforum.com/member.php...fo&userid=7078
View this thread: http://www.excelforum.com/showthread...hreadid=380925