selecting cell range in other worksheet without switching to worksheet

**suzetter** · 06-21-2005, 12:49 PM

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

Jim Thomlinson · 06-21-2005, 03:05 PM

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
>
>

**suzetter** · 06-21-2005, 04:20 PM

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.ClearContents
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

**bhofsetz** · 06-21-2005, 04:38 PM

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