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.

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.

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.

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!

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

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!

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!

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