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