Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active worksheet problem
I have a subroutine that has been working fine until I added 3 worksheets
from another workbook. It seems that when I activate a worksheet then refer to the ActiveSheet that I am getting a different worksheet. Here is a portion of the code. Worksheets("RatesByLevel").Activate b_end = False '*** First row contains query name and 2nd row contains Column headings so start at the 3nd row on the sheet '*** by initalizing the counter to 2. i_rownum = 2 '*** Using Base 0 array so set the indexes to their initial setting i_indxGeo = 0 i_indxLoc = 0 '*** Loop through the entire worksheet until we hit a blank geo value ** Do Until b_end i_rownum = i_rownum + 1 '*** End the loop when we come to a blank row *** If ActiveSheet.Range("C" & i_rownum) = "" Then b_end = True Else The worksheet activate is activating Sheet 12 but the ActiveSheet statement is accessing Sheet 20. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active worksheet problem
That is curious. In the VBEditor under your workbook project, is the sheet
you want the code to run on listed as: Sheet12 ("RatesByLevel") ? What is the tab name of Sheet20 ("Tab Name") Does anything in the list of sheets look out of order or unusual? Is there any Worksheet_Activate code on Sheet12 that could be sending the focus to another sheet? Mike F "djd" wrote in message ... I have a subroutine that has been working fine until I added 3 worksheets from another workbook. It seems that when I activate a worksheet then refer to the ActiveSheet that I am getting a different worksheet. Here is a portion of the code. Worksheets("RatesByLevel").Activate b_end = False '*** First row contains query name and 2nd row contains Column headings so start at the 3nd row on the sheet '*** by initalizing the counter to 2. i_rownum = 2 '*** Using Base 0 array so set the indexes to their initial setting i_indxGeo = 0 i_indxLoc = 0 '*** Loop through the entire worksheet until we hit a blank geo value ** Do Until b_end i_rownum = i_rownum + 1 '*** End the loop when we come to a blank row *** If ActiveSheet.Range("C" & i_rownum) = "" Then b_end = True Else The worksheet activate is activating Sheet 12 but the ActiveSheet statement is accessing Sheet 20. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active worksheet problem
I have checked those worksheets as well as the 3 worksheets that I copied
into the workbook and didn't find any code. All the logic is in a module. I actually got the sheet numbers backward. The RatesByLevel is sheet 20 - the data is being pulled off sheet 12. Problem occurs later in the routine where I activate sheet 13 but the activesheet statement is pointing at sheet 7. I have 24 sheets in this workbook but I wouldn't think that would be problem. Right now I am going through and fully qualifying the references by replacing the activesheet.range with workbooks("sheetname").range to see if that gets me around the problem. "Mike Fogleman" wrote: That is curious. In the VBEditor under your workbook project, is the sheet you want the code to run on listed as: Sheet12 ("RatesByLevel") ? What is the tab name of Sheet20 ("Tab Name") Does anything in the list of sheets look out of order or unusual? Is there any Worksheet_Activate code on Sheet12 that could be sending the focus to another sheet? Mike F "djd" wrote in message ... I have a subroutine that has been working fine until I added 3 worksheets from another workbook. It seems that when I activate a worksheet then refer to the ActiveSheet that I am getting a different worksheet. Here is a portion of the code. Worksheets("RatesByLevel").Activate b_end = False '*** First row contains query name and 2nd row contains Column headings so start at the 3nd row on the sheet '*** by initalizing the counter to 2. i_rownum = 2 '*** Using Base 0 array so set the indexes to their initial setting i_indxGeo = 0 i_indxLoc = 0 '*** Loop through the entire worksheet until we hit a blank geo value ** Do Until b_end i_rownum = i_rownum + 1 '*** End the loop when we come to a blank row *** If ActiveSheet.Range("C" & i_rownum) = "" Then b_end = True Else The worksheet activate is activating Sheet 12 but the ActiveSheet statement is accessing Sheet 20. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Active worksheet problem
That sounds like a good idea. You rarely need to activate a sheet, so just
qualifying the sheets and their ranges is just good code practice and it runs smoother in the background. Good Luck, Mike F "djd" wrote in message ... I have checked those worksheets as well as the 3 worksheets that I copied into the workbook and didn't find any code. All the logic is in a module. I actually got the sheet numbers backward. The RatesByLevel is sheet 20 - the data is being pulled off sheet 12. Problem occurs later in the routine where I activate sheet 13 but the activesheet statement is pointing at sheet 7. I have 24 sheets in this workbook but I wouldn't think that would be problem. Right now I am going through and fully qualifying the references by replacing the activesheet.range with workbooks("sheetname").range to see if that gets me around the problem. "Mike Fogleman" wrote: That is curious. In the VBEditor under your workbook project, is the sheet you want the code to run on listed as: Sheet12 ("RatesByLevel") ? What is the tab name of Sheet20 ("Tab Name") Does anything in the list of sheets look out of order or unusual? Is there any Worksheet_Activate code on Sheet12 that could be sending the focus to another sheet? Mike F "djd" wrote in message ... I have a subroutine that has been working fine until I added 3 worksheets from another workbook. It seems that when I activate a worksheet then refer to the ActiveSheet that I am getting a different worksheet. Here is a portion of the code. Worksheets("RatesByLevel").Activate b_end = False '*** First row contains query name and 2nd row contains Column headings so start at the 3nd row on the sheet '*** by initalizing the counter to 2. i_rownum = 2 '*** Using Base 0 array so set the indexes to their initial setting i_indxGeo = 0 i_indxLoc = 0 '*** Loop through the entire worksheet until we hit a blank geo value ** Do Until b_end i_rownum = i_rownum + 1 '*** End the loop when we come to a blank row *** If ActiveSheet.Range("C" & i_rownum) = "" Then b_end = True Else The worksheet activate is activating Sheet 12 but the ActiveSheet statement is accessing Sheet 20. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying the active worksheet to another worksheet in the same work | Excel Worksheet Functions | |||
Basic Question - How do I return the worksheet number of the active worksheet? | Excel Programming | |||
Active X Problem | Excel Programming | |||
Altering code to reference the worksheet before the active worksheet | Excel Programming | |||
macro to apply worksheet event to active worksheet | Excel Programming |