Macro runs on certain worksheets, not on others

[davegb]

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.

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
>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.
>

[davegb]

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.

[davegb]

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!

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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!
>

[davegb]

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" <[email protected]> wrote in message news:[email protected]...
> > 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!
> >

[davegb]

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