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 |
selecting cell range in other worksheet without switching to works
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 |
selecting cell range in other worksheet without switching to worksheet
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 |
selecting cell range in other worksheet without switching to worksheet
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 |
selecting cell range in other worksheet without switching to worksheet
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 |
All times are GMT +1. The time now is 03:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com