Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
I have a workbook that contains 13 worksheets, will have about 25 when
this iteration is done, maybe more later. I have a macro that determines a filter range on all the sheets in the workbook. I don't know how to change it so that only certain worksheets are effected by it. At this point, the ones with "Records" at the end of the sheet name should NOT be effected by it, the others should. In other words sheets with the name "XXX" it should determine the filter range, but sheets with the name "XXX Records", the macro should leave alone. How would I make this test? I appreciate the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
Hi Dave
You can use the Right function in the loop to see if the last 7 characters are "Records" If Right(sh.Name, 7) = "Records" Then .................. -- Regards Ron de Bruin http://www.rondebruin.nl "davegb" wrote in message oups.com... I have a workbook that contains 13 worksheets, will have about 25 when this iteration is done, maybe more later. I have a macro that determines a filter range on all the sheets in the workbook. I don't know how to change it so that only certain worksheets are effected by it. At this point, the ones with "Records" at the end of the sheet name should NOT be effected by it, the others should. In other words sheets with the name "XXX" it should determine the filter range, but sheets with the name "XXX Records", the macro should leave alone. How would I make this test? I appreciate the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
Thanks, Ron! I'm having trouble understanding how to make the loop
work. Since I want it to skip the rest of the routine if the If statement is true, how do I tell it to just go the the next worksheet and skip the steps in between? Right now, the macro is: Sub MFilterRange() Dim rFilterRange As Range Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets With Sh .Range(.Range("B3"), ..Range("B3").End(xlDown).End(xlToRight) _ .Offset(-1, 2)).Name = "'" & Sh.Name & "'!FilterRange" End With Next Sh End Sub When I put the if statement after the "for each" statement, how do I tell it to skip the rest of the routine? With a Goto? And if I use a Goto, then goto what? Thanks for the help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
Ron,
I tried a few things, and this seems to work. Can you tell me if you see anything wrong with it? Sub MFilterRange() Dim rFilterRange As Range Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets If Not Right(Sh.Name, 7) = "Records" Then With Sh .Range(.Range("B3"), ..Range("B3").End(xlDown).End(xlToRight) _ .Offset(-1, 2)).Name = "'" & Sh.Name & "'!FilterRange" End With End If Next Sh End Sub Thanks for the help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
Sub MFilterRange() End Sub Dim rFilterRange As Range Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets If Right(Sh.Name, 7) < "Records" Then With Sh.Range(.Range("B3"), .Range("B3").End(xlDown).End(xlToRight) _ ..Offset(-1, 2)).Name = "'" & Sh.Name & "'!FilterRange" End With End If Next Sh -- Mountain_Dewski ------------------------------------------------------------------------ Mountain_Dewski's Profile: http://www.excelforum.com/member.php...o&userid=19207 View this thread: http://www.excelforum.com/showthread...hreadid=373735 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
Hi Dave
If there is a empty cell in the B column the range stop there Is that what you want ? -- Regards Ron de Bruin http://www.rondebruin.nl "davegb" wrote in message ups.com... Ron, I tried a few things, and this seems to work. Can you tell me if you see anything wrong with it? Sub MFilterRange() Dim rFilterRange As Range Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets If Not Right(Sh.Name, 7) = "Records" Then With Sh .Range(.Range("B3"), .Range("B3").End(xlDown).End(xlToRight) _ .Offset(-1, 2)).Name = "'" & Sh.Name & "'!FilterRange" End With End If Next Sh End Sub Thanks for the help! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
No, not empty. There are merged cells with descriptions in rows 1 and 2
and Column A, so the continuous data starts at B3, down and to the right. Ron de Bruin wrote: Hi Dave If there is a empty cell in the B column the range stop there Is that what you want ? -- Regards Ron de Bruin http://www.rondebruin.nl "davegb" wrote in message ups.com... Ron, I tried a few things, and this seems to work. Can you tell me if you see anything wrong with it? Sub MFilterRange() Dim rFilterRange As Range Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets If Not Right(Sh.Name, 7) = "Records" Then With Sh .Range(.Range("B3"), .Range("B3").End(xlDown).End(xlToRight) _ .Offset(-1, 2)).Name = "'" & Sh.Name & "'!FilterRange" End With End If Next Sh End Sub Thanks for the help! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro runs on certain worksheets, not on others
Thanks for the help! Words fine.
Mountain_Dewski wrote: Sub MFilterRange() End Sub Dim rFilterRange As Range Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets If Right(Sh.Name, 7) < "Records" Then With Sh.Range(.Range("B3"), .Range("B3").End(xlDown).End(xlToRight) _ .Offset(-1, 2)).Name = "'" & Sh.Name & "'!FilterRange" End With End If Next Sh -- Mountain_Dewski ------------------------------------------------------------------------ Mountain_Dewski's Profile: http://www.excelforum.com/member.php...o&userid=19207 View this thread: http://www.excelforum.com/showthread...hreadid=373735 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA macro runs fine, but freezes if I try to do ANYTHING else whileit runs | Setting up and Configuration of Excel | |||
One macro runs then it auto runs another macro | Excel Discussion (Misc queries) | |||
Macro that runs in the background | Excel Discussion (Misc queries) | |||
Which Macro Runs...? | Excel Discussion (Misc queries) | |||
Macro Runs if Template name is ..... | Excel Programming |