I have code in one WkBookA, that when triggered performs some code to
make changes in WkBookB. However, when the macro is triggered from
WkBookA, WkSheetB1 is selected in WkBookB, but the code has to make
changes in WkSheetB2 in WkBookB. I didn't realize it before, but while
I was testing the code, WkSheetB2 would always be the active worksheet
and the code would work perfectly. Then when I tried it the way the
users would have to use it, i.e. WkSheetB1 would be the active
worksheet, I get the following error "Run-time error '1004': Select
method of Range class failed". Now I can fix the problem by selecting
WkSheetB2 just before I select the cell range, but I don't want the
users to see WkSheetB2 at all...this sheet just does some background
calculation and should not be shown to the users.

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

If IsItOpen(WkBookName) Then
'The Workbook is open. Perform the following assignments
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

LastRow = myworksheet.Cells.Find(What:="*",
After:=myworksheet.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
If LastRow 4 Then
RangeToClear = Col1 & "5:" & Col2 & LastRow
' I have to put "myworksheet.Select" here in order for the code
to work because it will not be the active worksheet
myworksheet.Range(RangeToClear).Select 'This is the line the error
occurs on as the first instance of myworksheet...select
Selection.ClearContents

RangeToClear = Col3 & "6:" & Col4 & LastRow
' I have to put "myworksheet.Select" here in order for the code
to work because it will not be the active worksheet
myworksheet.Range(RangeToClear).Select
MsgBox "Clear the second range"
Selection.ClearContents
End If

If myworksheet.Range(ArrayRange).Value < "None" Then
IRArray = myworksheet.Range(ArrayRange).Value
' I have to put "myworksheet.Select" here in order for the code
to work because it will not be the active worksheet
myworksheet.Range(IRArray).Select
Selection.FormulaArray = PICompDatFormula

IRArray = myworksheet.Range(Range2).Value
'myworksheet.Select
myworksheet.Range(IRArray).Select
Selection.FillDown
End If

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

End Sub


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

It is a little difficult to tell what is going on in your code since I do not
know the values of the arguments and such (or even exactly what is suppoesed
to happen. Here is some sample code for dealing with more than one workbook
that may be of help to you. It defines everything in terms of source and
destination which may be of some help.

Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")
Set rngSource = wksSource.Range("A1")

'Set your destination
On Error GoTo OpenBook
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo 0
Set wksDestination = wbkDestination.Sheets("Sheet1")
Set rngDestination = wksDestination.Range("A1")

'You now have all of your souce and destination objects

rngSource.Copy rngDestination

Exit Sub

OpenBook:
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
Resume Next
Exit Sub

End Sub
--
HTH...

Jim Thomlinson


"suzetter" wrote:


I have code in one WkBookA, that when triggered performs some code to
make changes in WkBookB. However, when the macro is triggered from
WkBookA, WkSheetB1 is selected in WkBookB, but the code has to make
changes in WkSheetB2 in WkBookB. I didn't realize it before, but while
I was testing the code, WkSheetB2 would always be the active worksheet
and the code would work perfectly. Then when I tried it the way the
users would have to use it, i.e. WkSheetB1 would be the active
worksheet, I get the following error "Run-time error '1004': Select
method of Range class failed". Now I can fix the problem by selecting
WkSheetB2 just before I select the cell range, but I don't want the
users to see WkSheetB2 at all...this sheet just does some background
calculation and should not be shown to the users.

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

If IsItOpen(WkBookName) Then
'The Workbook is open. Perform the following assignments
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

LastRow = myworksheet.Cells.Find(What:="*",
After:=myworksheet.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
If LastRow 4 Then
RangeToClear = Col1 & "5:" & Col2 & LastRow
' I have to put "myworksheet.Select" here in order for the code
to work because it will not be the active worksheet
myworksheet.Range(RangeToClear).Select 'This is the line the error
occurs on as the first instance of myworksheet...select
Selection.ClearContents

RangeToClear = Col3 & "6:" & Col4 & LastRow
' I have to put "myworksheet.Select" here in order for the code
to work because it will not be the active worksheet
myworksheet.Range(RangeToClear).Select
MsgBox "Clear the second range"
Selection.ClearContents
End If

If myworksheet.Range(ArrayRange).Value < "None" Then
IRArray = myworksheet.Range(ArrayRange).Value
' I have to put "myworksheet.Select" here in order for the code
to work because it will not be the active worksheet
myworksheet.Range(IRArray).Select
Selection.FormulaArray = PICompDatFormula

IRArray = myworksheet.Range(Range2).Value
'myworksheet.Select
myworksheet.Range(IRArray).Select
Selection.FillDown
End If

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

End Sub


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

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

You can do much of your manipulations without having to select the
ranges on the other sheets. Try simplifying the three lines of code
you had highlighted in red from your first post.

myworksheet.Range(RangeToClear).Select
Selection.ClearContents

change this to

myworksheet.Range(RangeToClear).ClearContents

And likewise for the other problem code

myworksheet.Range(RangeToClear).Select
MsgBox "Clear the second range"
Selection.ClearContents
Change To
MsgBox "Clear the second range"
myworksheet.Range(RangeToClear).ClearContents

myworksheet.Range(IRArray).Select
Selection.FormulaArray = PICompDatFormula
Change To
myworksheet.Range(IRArray).FormulaArray = PICompDatFormula

This should make your code run faster, make it easier to read AND keep
the user from seeing the screen flash as different sheets are selected
plus keep them on the "DYREPMST" worksheet.

Give those changes and let us know if they did the trick.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380925

Thank you for that simple but effective solution
It worked perfectly...I don't know how I didn't think of that, it was
so simple...duhhhhhhhh!


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