| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Worksheet
The code below is an excerpt from a larger macro. I am
working in worksheet "A" and want to activate a worksheet called "B", if it exists. The way the code is written now, I imagine that the macro will error out if it does not find a worksheet named "B." What code can I build in to ignore this section of code if the worksheet does not exist? TIA. (I am working in worksheet "A" before I get to this section of code.) Workbooks("FIR_PATs.xls").Worksheets("B").Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Worksheet
Dim sh as Worksheet
on error resume next set sh = Workbooks("FIR_PATs.xls").Worksheets("B") On Error goto 0 if not sh is nothing then sh.parent.Activate sh.Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End if -- Regards, Tom Ogilvy "Paul" wrote in message ... The code below is an excerpt from a larger macro. I am working in worksheet "A" and want to activate a worksheet called "B", if it exists. The way the code is written now, I imagine that the macro will error out if it does not find a worksheet named "B." What code can I build in to ignore this section of code if the worksheet does not exist? TIA. (I am working in worksheet "A" before I get to this section of code.) Workbooks("FIR_PATs.xls").Worksheets("B").Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Worksheet
This worked well. However, I attempted to use the routine
twice in my module, looking for worksheet "B" in one section and then looking for worksheet "C" in another section. If it found "B", when the code got to looking for "C" it did not skip the code section, even though "C" was not in the workbook. In the second iteration of the module I copied the code from line two through the end. Is it obvious to you what I am doing wrong? Do I need to "reset" the worksheet variable or should I not be repeating the "on error resume" or "on error goto 0"? -----Original Message----- Dim sh as Worksheet on error resume next set sh = Workbooks("FIR_PATs.xls").Worksheets("B") On Error goto 0 if not sh is nothing then sh.parent.Activate sh.Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End if -- Regards, Tom Ogilvy "Paul" wrote in message ... The code below is an excerpt from a larger macro. I am working in worksheet "A" and want to activate a worksheet called "B", if it exists. The way the code is written now, I imagine that the macro will error out if it does not find a worksheet named "B." What code can I build in to ignore this section of code if the worksheet does not exist? TIA. (I am working in worksheet "A" before I get to this section of code.) Workbooks("FIR_PATs.xls").Worksheets("B").Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Worksheet
Dim sh as Worksheet
on error resume next set sh = Workbooks("FIR_PATs.xls").Worksheets("B") On Error goto 0 if not sh is nothing then sh.parent.Activate sh.Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End if Set sh = Nothing '<=== Add this line on error resume next set sh = Workbooks("FIR_PATs.xls").Worksheets("C") On Error goto 0 if not sh is nothing then sh.parent.Activate sh.Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End if -- Regards, Tom Ogilvy "Paul" wrote in message ... This worked well. However, I attempted to use the routine twice in my module, looking for worksheet "B" in one section and then looking for worksheet "C" in another section. If it found "B", when the code got to looking for "C" it did not skip the code section, even though "C" was not in the workbook. In the second iteration of the module I copied the code from line two through the end. Is it obvious to you what I am doing wrong? Do I need to "reset" the worksheet variable or should I not be repeating the "on error resume" or "on error goto 0"? -----Original Message----- Dim sh as Worksheet on error resume next set sh = Workbooks("FIR_PATs.xls").Worksheets("B") On Error goto 0 if not sh is nothing then sh.parent.Activate sh.Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End if -- Regards, Tom Ogilvy "Paul" wrote in message ... The code below is an excerpt from a larger macro. I am working in worksheet "A" and want to activate a worksheet called "B", if it exists. The way the code is written now, I imagine that the macro will error out if it does not find a worksheet named "B." What code can I build in to ignore this section of code if the worksheet does not exist? TIA. (I am working in worksheet "A" before I get to this section of code.) Workbooks("FIR_PATs.xls").Worksheets("B").Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Worksheet
Try an error handler:
On Error Resume Next Workbooks("FIR_PATs.xls").Worksheets("B").Activate If Err.Number = 0 Then On Error GoTo 0 total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End If On Error GoTo 0 -- Message posted from http://www.ExcelForum.com |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Worksheet
Here's my function that does it. Your code would use liek so
If IsWsSheet("B",Workbooks("FIR_PATs.xls")) Then Workbooks("FIR_PATs.xls").Worksheets("B").Activate total_row = Range("A65536").End(xlUp).Row .Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With End If '----------------------------------------------------------------- Function IsWsSheet(Sh As String, Optional WB As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If WB Is Nothing Then Set WB = ActiveWorkbook On Error Resume Next Set oWs = WB.Worksheets(Sh) On Error GoTo 0 IsWsSheet = Not oWs Is Nothing End Function -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Paul" wrote in message ... The code below is an excerpt from a larger macro. I am working in worksheet "A" and want to activate a worksheet called "B", if it exists. The way the code is written now, I imagine that the macro will error out if it does not find a worksheet named "B." What code can I build in to ignore this section of code if the worksheet does not exist? TIA. (I am working in worksheet "A" before I get to this section of code.) Workbooks("FIR_PATs.xls").Worksheets("B").Activate total_row = Range("A65536").End(xlUp).Row Range("A" & total_row - 2).Select With ActiveCell TtlD = .Offset(0, 3).Value TtlE = .Offset(0, 4).Value TtlF = .Offset(0, 5).Value TtlG = .Offset(0, 6).Value TtlH = .Offset(0, 7).Value TtlI = .Offset(0, 8).Value End With |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Test cell interior color by worksheet function? | Excel Worksheet Functions | |||
| Merged Cells in Worksheet, Test for | Excel Discussion (Misc queries) | |||
| If a worksheet name is = to test then a msgbox appears | Excel Discussion (Misc queries) | |||
| How can I test when any worksheet within a workbook is selected | Excel Programming | |||
| Test to see if a worksheet exists thanks, Chip | Excel Programming | |||
Hybrid Mode
