Go To Next Worksheet in Loop

cfatz1@gmail.com · 08-21-2006, 02:40 PM

Hi-

I have the following code. When it runs I get a "Subscript out of
range" error. Can anyone clean this up for me and get me to the next
sheet in the workbook? Thanks in advance for your help!

For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
Range("B2").Select
If ActiveCell.Value = "Distributor:" Then
FUNCTION1
End If
Sheets(x).Select
Next x

Thanks,
Chris

Don Guillett · 08-21-2006, 03:00 PM

No need to select

For i = ActiveSheet.Index To Worksheets.Count
If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
Next i

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<cfatz1@gmail.com> wrote in message
news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
> Hi-
>
> I have the following code. When it runs I get a "Subscript out of
> range" error. Can anyone clean this up for me and get me to the next
> sheet in the workbook? Thanks in advance for your help!
>
> For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
> Range("B2").Select
> If ActiveCell.Value = "Distributor:" Then
> FUNCTION1
> End If
> Sheets(x).Select
> Next x
>
> Thanks,
> Chris
>

cfatz1@gmail.com · 08-21-2006, 04:05 PM

Thanks for the reply Don.

This code does not move to the next sheet....

-Chris

Don Guillett wrote:
> No need to select
>
> For i = ActiveSheet.Index To Worksheets.Count
> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
> Next i
>
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> <cfatz1@gmail.com> wrote in message
> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
> > Hi-
> >
> > I have the following code. When it runs I get a "Subscript out of
> > range" error. Can anyone clean this up for me and get me to the next
> > sheet in the workbook? Thanks in advance for your help!
> >
> > For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
> > Range("B2").Select
> > If ActiveCell.Value = "Distributor:" Then
> > FUNCTION1
> > End If
> > Sheets(x).Select
> > Next x
> >
> > Thanks,
> > Chris
> >

Don Guillett · 08-21-2006, 06:05 PM

That is correct and by design. That is because you rarely have to select to
do what you want. Why waste time and effort doing that?

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<cfatz1@gmail.com> wrote in message
news:1156190417.514964.56220@p79g2000cwp.googlegroups.com...
> Thanks for the reply Don.
>
> This code does not move to the next sheet....
>
> -Chris
>
> Don Guillett wrote:
>> No need to select
>>
>> For i = ActiveSheet.Index To Worksheets.Count
>> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
>> Next i
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> <cfatz1@gmail.com> wrote in message
>> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
>> > Hi-
>> >
>> > I have the following code. When it runs I get a "Subscript out of
>> > range" error. Can anyone clean this up for me and get me to the next
>> > sheet in the workbook? Thanks in advance for your help!
>> >
>> > For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
>> > Range("B2").Select
>> > If ActiveCell.Value = "Distributor:" Then
>> > FUNCTION1
>> > End If
>> > Sheets(x).Select
>> > Next x
>> >
>> > Thanks,
>> > Chris
>> >
>

Don Guillett · 08-21-2006, 06:10 PM

OOOOOOOh MY bad!!

For i = ActiveSheet.Index To Worksheets.Count
If UCase(sheets(i).Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
Next i

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:%23s4XE1WxGHA.4960@TK2MSFTNGP04.phx.gbl...
> That is correct and by design. That is because you rarely have to select
> to do what you want. Why waste time and effort doing that?
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> <cfatz1@gmail.com> wrote in message
> news:1156190417.514964.56220@p79g2000cwp.googlegroups.com...
>> Thanks for the reply Don.
>>
>> This code does not move to the next sheet....
>>
>> -Chris
>>
>> Don Guillett wrote:
>>> No need to select
>>>
>>> For i = ActiveSheet.Index To Worksheets.Count
>>> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
>>> Next i
>>>
>>>
>>> --
>>> Don Guillett
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> <cfatz1@gmail.com> wrote in message
>>> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
>>> > Hi-
>>> >
>>> > I have the following code. When it runs I get a "Subscript out of
>>> > range" error. Can anyone clean this up for me and get me to the next
>>> > sheet in the workbook? Thanks in advance for your help!
>>> >
>>> > For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
>>> > Range("B2").Select
>>> > If ActiveCell.Value = "Distributor:" Then
>>> > FUNCTION1
>>> > End If
>>> > Sheets(x).Select
>>> > Next x
>>> >
>>> > Thanks,
>>> > Chris
>>> >
>>
>
>

cfatz1@gmail.com · 08-22-2006, 11:50 AM

Hi Don-

Now when I run the macro it disregards the value in "B2" and just runs
the function on all sheets. The value in B2 has to be "Distributor:"
in order for the function to run.

Sorry for being a pain! You are helping me tremendously and I
appreciate the effort!

Thanks Again,
Chris

Don Guillett wrote:
> OOOOOOOh MY bad!!
>
> For i = ActiveSheet.Index To Worksheets.Count
> If UCase(sheets(i).Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
> Next i
>
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:%23s4XE1WxGHA.4960@TK2MSFTNGP04.phx.gbl...
> > That is correct and by design. That is because you rarely have to select
> > to do what you want. Why waste time and effort doing that?
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > <cfatz1@gmail.com> wrote in message
> > news:1156190417.514964.56220@p79g2000cwp.googlegroups.com...
> >> Thanks for the reply Don.
> >>
> >> This code does not move to the next sheet....
> >>
> >> -Chris
> >>
> >> Don Guillett wrote:
> >>> No need to select
> >>>
> >>> For i = ActiveSheet.Index To Worksheets.Count
> >>> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
> >>> Next i
> >>>
> >>>
> >>> --
> >>> Don Guillett
> >>> SalesAid Software
> >>> dguillett1@austin.rr.com
> >>> <cfatz1@gmail.com> wrote in message
> >>> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
> >>> > Hi-
> >>> >
> >>> > I have the following code. When it runs I get a "Subscript out of
> >>> > range" error. Can anyone clean this up for me and get me to the next
> >>> > sheet in the workbook? Thanks in advance for your help!
> >>> >
> >>> > For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
> >>> > Range("B2").Select
> >>> > If ActiveCell.Value = "Distributor:" Then
> >>> > FUNCTION1
> >>> > End If
> >>> > Sheets(x).Select
> >>> > Next x
> >>> >
> >>> > Thanks,
> >>> > Chris
> >>> >
> >>
> >
> >

Don Guillett · 08-22-2006, 12:20 PM

I just tested this macro with a new workbook with Distributor: on sheet 3
and 5 and firing the macro from sheet 2. Worked just fine. I have no idea
what function1 is? You may send me a workbook, if desired.

Sub findDistributor()
For i = ActiveSheet.Index To Worksheets.Count
If UCase(Sheets(i).Range("B2")) = "DISTRIBUTOR:" Then _
MsgBox Sheets(i).Name
Next i
End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<cfatz1@gmail.com> wrote in message
news:1156261541.653115.224940@74g2000cwt.googlegroups.com...
> Hi Don-
>
> Now when I run the macro it disregards the value in "B2" and just runs
> the function on all sheets. The value in B2 has to be "Distributor:"
> in order for the function to run.
>
> Sorry for being a pain! You are helping me tremendously and I
> appreciate the effort!
>
> Thanks Again,
> Chris
>
> Don Guillett wrote:
>> OOOOOOOh MY bad!!
>>
>> For i = ActiveSheet.Index To Worksheets.Count
>> If UCase(sheets(i).Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
>> Next i
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
>> news:%23s4XE1WxGHA.4960@TK2MSFTNGP04.phx.gbl...
>> > That is correct and by design. That is because you rarely have to
>> > select
>> > to do what you want. Why waste time and effort doing that?
>> >
>> > --
>> > Don Guillett
>> > SalesAid Software
>> > dguillett1@austin.rr.com
>> > <cfatz1@gmail.com> wrote in message
>> > news:1156190417.514964.56220@p79g2000cwp.googlegroups.com...
>> >> Thanks for the reply Don.
>> >>
>> >> This code does not move to the next sheet....
>> >>
>> >> -Chris
>> >>
>> >> Don Guillett wrote:
>> >>> No need to select
>> >>>
>> >>> For i = ActiveSheet.Index To Worksheets.Count
>> >>> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
>> >>> Next i
>> >>>
>> >>>
>> >>> --
>> >>> Don Guillett
>> >>> SalesAid Software
>> >>> dguillett1@austin.rr.com
>> >>> <cfatz1@gmail.com> wrote in message
>> >>> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
>> >>> > Hi-
>> >>> >
>> >>> > I have the following code. When it runs I get a "Subscript out of
>> >>> > range" error. Can anyone clean this up for me and get me to the
>> >>> > next
>> >>> > sheet in the workbook? Thanks in advance for your help!
>> >>> >
>> >>> > For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
>> >>> > Range("B2").Select
>> >>> > If ActiveCell.Value = "Distributor:" Then
>> >>> > FUNCTION1
>> >>> > End If
>> >>> > Sheets(x).Select
>> >>> > Next x
>> >>> >
>> >>> > Thanks,
>> >>> > Chris
>> >>> >
>> >>
>> >
>> >
>

cfatz1@gmail.com · 08-22-2006, 05:35 PM

Don-

It works! My bad. I had to add the following code before I run
FUNCTION1.

Sheets(i).Select

I had to select the sheet in order for the function to run on that
sheet. Everything is working ok. Thanks for your help and for
checking back today.

-Chris

Don Guillett wrote:
> I just tested this macro with a new workbook with Distributor: on sheet 3
> and 5 and firing the macro from sheet 2. Worked just fine. I have no idea
> what function1 is? You may send me a workbook, if desired.
>
> Sub findDistributor()
> For i = ActiveSheet.Index To Worksheets.Count
> If UCase(Sheets(i).Range("B2")) = "DISTRIBUTOR:" Then _
> MsgBox Sheets(i).Name
> Next i
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> <cfatz1@gmail.com> wrote in message
> news:1156261541.653115.224940@74g2000cwt.googlegroups.com...
> > Hi Don-
> >
> > Now when I run the macro it disregards the value in "B2" and just runs
> > the function on all sheets. The value in B2 has to be "Distributor:"
> > in order for the function to run.
> >
> > Sorry for being a pain! You are helping me tremendously and I
> > appreciate the effort!
> >
> > Thanks Again,
> > Chris
> >
> > Don Guillett wrote:
> >> OOOOOOOh MY bad!!
> >>
> >> For i = ActiveSheet.Index To Worksheets.Count
> >> If UCase(sheets(i).Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
> >> Next i
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> dguillett1@austin.rr.com
> >> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> >> news:%23s4XE1WxGHA.4960@TK2MSFTNGP04.phx.gbl...
> >> > That is correct and by design. That is because you rarely have to
> >> > select
> >> > to do what you want. Why waste time and effort doing that?
> >> >
> >> > --
> >> > Don Guillett
> >> > SalesAid Software
> >> > dguillett1@austin.rr.com
> >> > <cfatz1@gmail.com> wrote in message
> >> > news:1156190417.514964.56220@p79g2000cwp.googlegroups.com...
> >> >> Thanks for the reply Don.
> >> >>
> >> >> This code does not move to the next sheet....
> >> >>
> >> >> -Chris
> >> >>
> >> >> Don Guillett wrote:
> >> >>> No need to select
> >> >>>
> >> >>> For i = ActiveSheet.Index To Worksheets.Count
> >> >>> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
> >> >>> Next i
> >> >>>
> >> >>>
> >> >>> --
> >> >>> Don Guillett
> >> >>> SalesAid Software
> >> >>> dguillett1@austin.rr.com
> >> >>> <cfatz1@gmail.com> wrote in message
> >> >>> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
> >> >>> > Hi-
> >> >>> >
> >> >>> > I have the following code. When it runs I get a "Subscript out of
> >> >>> > range" error. Can anyone clean this up for me and get me to the
> >> >>> > next
> >> >>> > sheet in the workbook? Thanks in advance for your help!
> >> >>> >
> >> >>> > For x = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index
> >> >>> > Range("B2").Select
> >> >>> > If ActiveCell.Value = "Distributor:" Then
> >> >>> > FUNCTION1
> >> >>> > End If
> >> >>> > Sheets(x).Select
> >> >>> > Next x
> >> >>> >
> >> >>> > Thanks,
> >> >>> > Chris
> >> >>> >
> >> >>
> >> >
> >> >
> >

Don Guillett · 08-22-2006, 05:40 PM

You should NOT have to select. Perhaps a re-write of whatever function1 is.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<cfatz1@gmail.com> wrote in message
news:1156282239.950482.92980@i3g2000cwc.googlegroups.com...
> Don-
>
> It works! My bad. I had to add the following code before I run
> FUNCTION1.
>
> Sheets(i).Select
>
> I had to select the sheet in order for the function to run on that
> sheet. Everything is working ok. Thanks for your help and for
> checking back today.
>
> -Chris
>
> Don Guillett wrote:
>> I just tested this macro with a new workbook with Distributor: on sheet 3
>> and 5 and firing the macro from sheet 2. Worked just fine. I have no idea
>> what function1 is? You may send me a workbook, if desired.
>>
>> Sub findDistributor()
>> For i = ActiveSheet.Index To Worksheets.Count
>> If UCase(Sheets(i).Range("B2")) = "DISTRIBUTOR:" Then _
>> MsgBox Sheets(i).Name
>> Next i
>> End Sub
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> <cfatz1@gmail.com> wrote in message
>> news:1156261541.653115.224940@74g2000cwt.googlegroups.com...
>> > Hi Don-
>> >
>> > Now when I run the macro it disregards the value in "B2" and just runs
>> > the function on all sheets. The value in B2 has to be "Distributor:"
>> > in order for the function to run.
>> >
>> > Sorry for being a pain! You are helping me tremendously and I
>> > appreciate the effort!
>> >
>> > Thanks Again,
>> > Chris
>> >
>> > Don Guillett wrote:
>> >> OOOOOOOh MY bad!!
>> >>
>> >> For i = ActiveSheet.Index To Worksheets.Count
>> >> If UCase(sheets(i).Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
>> >> Next i
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> dguillett1@austin.rr.com
>> >> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
>> >> news:%23s4XE1WxGHA.4960@TK2MSFTNGP04.phx.gbl...
>> >> > That is correct and by design. That is because you rarely have to
>> >> > select
>> >> > to do what you want. Why waste time and effort doing that?
>> >> >
>> >> > --
>> >> > Don Guillett
>> >> > SalesAid Software
>> >> > dguillett1@austin.rr.com
>> >> > <cfatz1@gmail.com> wrote in message
>> >> > news:1156190417.514964.56220@p79g2000cwp.googlegroups.com...
>> >> >> Thanks for the reply Don.
>> >> >>
>> >> >> This code does not move to the next sheet....
>> >> >>
>> >> >> -Chris
>> >> >>
>> >> >> Don Guillett wrote:
>> >> >>> No need to select
>> >> >>>
>> >> >>> For i = ActiveSheet.Index To Worksheets.Count
>> >> >>> If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1
>> >> >>> Next i
>> >> >>>
>> >> >>>
>> >> >>> --
>> >> >>> Don Guillett
>> >> >>> SalesAid Software
>> >> >>> dguillett1@austin.rr.com
>> >> >>> <cfatz1@gmail.com> wrote in message
>> >> >>> news:1156185314.082838.94380@m73g2000cwd.googlegroups.com...
>> >> >>> > Hi-
>> >> >>> >
>> >> >>> > I have the following code. When it runs I get a "Subscript out
>> >> >>> > of
>> >> >>> > range" error. Can anyone clean this up for me and get me to the
>> >> >>> > next
>> >> >>> > sheet in the workbook? Thanks in advance for your help!
>> >> >>> >
>> >> >>> > For x = ActiveSheet.Index + 1 To
>> >> >>> > Worksheets(Worksheets.Count).Index
>> >> >>> > Range("B2").Select
>> >> >>> > If ActiveCell.Value = "Distributor:" Then
>> >> >>> > FUNCTION1
>> >> >>> > End If
>> >> >>> > Sheets(x).Select
>> >> >>> > Next x
>> >> >>> >
>> >> >>> > Thanks,
>> >> >>> > Chris
>> >> >>> >
>> >> >>
>> >> >
>> >> >
>> >
>