Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting cell range in other worksheet without switching to worksheet
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
switching the worksheet? | Excel Discussion (Misc queries) | |||
selecting every eg 5th cell in a worksheet | Excel Worksheet Functions | |||
Not switching to next worksheet | Setting up and Configuration of Excel | |||
Selecting a Worksheet Range | Excel Worksheet Functions | |||
Selecting a range in a different worksheet | Excel Programming |