Make Macro go to Next Workseet

[Michael from Austin]

I have a macro that I want to have also run on the next worksheet and then
the next until there are no more worksheet. The worksheets have names. I do
not want it to have to look for the name and then open it. I just want it to
go to the next one and then the next until the end... Can this be done. Any
help would be appreceited.....
--
Regards,
Michael

[Tom Ogilvy]

for each sh in Activeworkbook.Worksheets

might be what you are looking for.

--
Regards,
Tom Ogilvy

"Michael from Austin" <MichaelfromAustin@discussions.microsoft.com> wrote in
message news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
> I have a macro that I want to have also run on the next worksheet and then
> the next until there are no more worksheet. The worksheets have names. I
do
> not want it to have to look for the name and then open it. I just want it
to
> go to the next one and then the next until the end... Can this be done.
Any
> help would be appreceited.....
> --
> Regards,
> Michael

[Chip Pearson]

Michael,

Try something like the following:

Dim Ndx As Integer
For Ndx = ActiveSheet.Index + 1 To
Worksheets(Worksheets.Count).Index
Worksheets(Ndx).Range("A1").Value = 123
Next Ndx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Michael from Austin"
<MichaelfromAustin@discussions.microsoft.com> wrote in message
news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
>I have a macro that I want to have also run on the next
>worksheet and then
> the next until there are no more worksheet. The worksheets have
> names. I do
> not want it to have to look for the name and then open it. I
> just want it to
> go to the next one and then the next until the end... Can this
> be done. Any
> help would be appreceited.....
> --
> Regards,
> Michael

[Michael from Austin]

I received a Run Time Error 424. "Object Required."

"Chip Pearson" wrote:

> Michael,
>
> Try something like the following:
>
> Dim Ndx As Integer
> For Ndx = ActiveSheet.Index + 1 To
> Worksheets(Worksheets.Count).Index
> Worksheets(Ndx).Range("A1").Value = 123
> Next Ndx
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Michael from Austin"
> <MichaelfromAustin@discussions.microsoft.com> wrote in message
> news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
> >I have a macro that I want to have also run on the next
> >worksheet and then
> > the next until there are no more worksheet. The worksheets have
> > names. I do
> > not want it to have to look for the name and then open it. I
> > just want it to
> > go to the next one and then the next until the end... Can this
> > be done. Any
> > help would be appreceited.....
> > --
> > Regards,
> > Michael
>
>
>

[Chip Pearson]

The code works as written for me. On what line do you get the
error message?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Michael from Austin"
<MichaelfromAustin@discussions.microsoft.com> wrote in message
news:C54039C4-BE58-433D-B4CA-1A4912595567@microsoft.com...
>I received a Run Time Error 424. "Object Required."
>
> "Chip Pearson" wrote:
>
>> Michael,
>>
>> Try something like the following:
>>
>> Dim Ndx As Integer
>> For Ndx = ActiveSheet.Index + 1 To
>> Worksheets(Worksheets.Count).Index
>> Worksheets(Ndx).Range("A1").Value = 123
>> Next Ndx
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "Michael from Austin"
>> <MichaelfromAustin@discussions.microsoft.com> wrote in message
>> news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
>> >I have a macro that I want to have also run on the next
>> >worksheet and then
>> > the next until there are no more worksheet. The worksheets
>> > have
>> > names. I do
>> > not want it to have to look for the name and then open it. I
>> > just want it to
>> > go to the next one and then the next until the end... Can
>> > this
>> > be done. Any
>> > help would be appreceited.....
>> > --
>> > Regards,
>> > Michael
>>
>>
>>

[Chip Pearson]

I should have added that

For Ndx = ActiveSheet.Index + 1 To
Worksheets(Worksheets.Count).Index

should all be on a single line of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Michael from Austin"
<MichaelfromAustin@discussions.microsoft.com> wrote in message
news:C54039C4-BE58-433D-B4CA-1A4912595567@microsoft.com...
>I received a Run Time Error 424. "Object Required."
>
> "Chip Pearson" wrote:
>
>> Michael,
>>
>> Try something like the following:
>>
>> Dim Ndx As Integer
>> For Ndx = ActiveSheet.Index + 1 To
>> Worksheets(Worksheets.Count).Index
>> Worksheets(Ndx).Range("A1").Value = 123
>> Next Ndx
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "Michael from Austin"
>> <MichaelfromAustin@discussions.microsoft.com> wrote in message
>> news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
>> >I have a macro that I want to have also run on the next
>> >worksheet and then
>> > the next until there are no more worksheet. The worksheets
>> > have
>> > names. I do
>> > not want it to have to look for the name and then open it. I
>> > just want it to
>> > go to the next one and then the next until the end... Can
>> > this
>> > be done. Any
>> > help would be appreceited.....
>> > --
>> > Regards,
>> > Michael
>>
>>
>>

[Michael from Austin]

It is all on one line. It errors, the same error as before, still. Do I need
to declare Ndx as a global variable?

"Chip Pearson" wrote:

> I should have added that
>
> For Ndx = ActiveSheet.Index + 1 To
> Worksheets(Worksheets.Count).Index
>
> should all be on a single line of code.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Michael from Austin"
> <MichaelfromAustin@discussions.microsoft.com> wrote in message
> news:C54039C4-BE58-433D-B4CA-1A4912595567@microsoft.com...
> >I received a Run Time Error 424. "Object Required."
> >
> > "Chip Pearson" wrote:
> >
> >> Michael,
> >>
> >> Try something like the following:
> >>
> >> Dim Ndx As Integer
> >> For Ndx = ActiveSheet.Index + 1 To
> >> Worksheets(Worksheets.Count).Index
> >> Worksheets(Ndx).Range("A1").Value = 123
> >> Next Ndx
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> "Michael from Austin"
> >> <MichaelfromAustin@discussions.microsoft.com> wrote in message
> >> news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
> >> >I have a macro that I want to have also run on the next
> >> >worksheet and then
> >> > the next until there are no more worksheet. The worksheets
> >> > have
> >> > names. I do
> >> > not want it to have to look for the name and then open it. I
> >> > just want it to
> >> > go to the next one and then the next until the end... Can
> >> > this
> >> > be done. Any
> >> > help would be appreceited.....
> >> > --
> >> > Regards,
> >> > Michael
> >>
> >>
> >>
>
>
>

[Chip Pearson]

No, you don't need to declare Ndx as a global variable. Just
declare it within the procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Michael from Austin"
<MichaelfromAustin@discussions.microsoft.com> wrote in message
news:277BAE26-B39A-4DA2-A190-6C21EA9F8077@microsoft.com...
> It is all on one line. It errors, the same error as before,
> still. Do I need
> to declare Ndx as a global variable?
>
> "Chip Pearson" wrote:
>
>> I should have added that
>>
>> For Ndx = ActiveSheet.Index + 1 To
>> Worksheets(Worksheets.Count).Index
>>
>> should all be on a single line of code.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "Michael from Austin"
>> <MichaelfromAustin@discussions.microsoft.com> wrote in message
>> news:C54039C4-BE58-433D-B4CA-1A4912595567@microsoft.com...
>> >I received a Run Time Error 424. "Object Required."
>> >
>> > "Chip Pearson" wrote:
>> >
>> >> Michael,
>> >>
>> >> Try something like the following:
>> >>
>> >> Dim Ndx As Integer
>> >> For Ndx = ActiveSheet.Index + 1 To
>> >> Worksheets(Worksheets.Count).Index
>> >> Worksheets(Ndx).Range("A1").Value = 123
>> >> Next Ndx
>> >>
>> >>
>> >>
>> >> --
>> >> Cordially,
>> >> Chip Pearson
>> >> Microsoft MVP - Excel
>> >> Pearson Software Consulting, LLC
>> >> www.cpearson.com
>> >>
>> >>
>> >>
>> >>
>> >> "Michael from Austin"
>> >> <MichaelfromAustin@discussions.microsoft.com> wrote in
>> >> message
>> >> news:C1412190-BA8A-4B54-8E55-FB4E0A0CD1F9@microsoft.com...
>> >> >I have a macro that I want to have also run on the next
>> >> >worksheet and then
>> >> > the next until there are no more worksheet. The
>> >> > worksheets
>> >> > have
>> >> > names. I do
>> >> > not want it to have to look for the name and then open
>> >> > it. I
>> >> > just want it to
>> >> > go to the next one and then the next until the end...
>> >> > Can
>> >> > this
>> >> > be done. Any
>> >> > help would be appreceited.....
>> >> > --
>> >> > Regards,
>> >> > Michael
>> >>
>> >>
>> >>
>>
>>
>>

[Michael from Austin]

Here is the code that I have entered.

Sub ChangeSheet()

' begins mac in sheet 1 cell a2
Dim Ndx As Integer
For Ndx = ActiveSheet.Index + 1 To Worksheets(Worksheet.Count).Index
Worksheets(Ndx).Range("A1").Value = 123
Next Ndx

Call ForNextLoop



End Sub

This is the error:

Run-Time Error '424' Expected an Object

I get this on the following line..

For Ndx = ActiveSheet.Index + 1 To Worksheets(Worksheet.Count).Index






"Michael from Austin" wrote:

> I have a macro that I want to have also run on the next worksheet and then
> the next until there are no more worksheet. The worksheets have names. I do
> not want it to have to look for the name and then open it. I just want it to
> go to the next one and then the next until the end... Can this be done. Any
> help would be appreceited.....
> --
> Regards,
> Michael

[Chip Pearson]

"Worksheets" must be plural.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Michael from Austin"
<MichaelfromAustin@discussions.microsoft.com> wrote in message
news:5BE32321-DF12-4208-AD08-863BF572E642@microsoft.com...
> Here is the code that I have entered.
>
> Sub ChangeSheet()
>
> ' begins mac in sheet 1 cell a2
> Dim Ndx As Integer
> For Ndx = ActiveSheet.Index + 1 To
> Worksheets(Worksheet.Count).Index
> Worksheets(Ndx).Range("A1").Value = 123
> Next Ndx
>
> Call ForNextLoop
>
>
>
> End Sub
>
> This is the error:
>
> Run-Time Error '424' Expected an Object
>
> I get this on the following line..
>
> For Ndx = ActiveSheet.Index + 1 To
> Worksheets(Worksheet.Count).Index
>
>
>
>
>
>
> "Michael from Austin" wrote:
>
>> I have a macro that I want to have also run on the next
>> worksheet and then
>> the next until there are no more worksheet. The worksheets
>> have names. I do
>> not want it to have to look for the name and then open it. I
>> just want it to
>> go to the next one and then the next until the end... Can
>> this be done. Any
>> help would be appreceited.....
>> --
>> Regards,
>> Michael

[Michael from Austin]

Cool. It doesn't error anymore, but it also does not go to the next
worksheet. All it does now is add the value"123" to A2 on worksheet 2 and
worksheet nth without actually going there?

"Chip Pearson" wrote:

> "Worksheets" must be plural.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Michael from Austin"
> <MichaelfromAustin@discussions.microsoft.com> wrote in message
> news:5BE32321-DF12-4208-AD08-863BF572E642@microsoft.com...
> > Here is the code that I have entered.
> >
> > Sub ChangeSheet()
> >
> > ' begins mac in sheet 1 cell a2
> > Dim Ndx As Integer
> > For Ndx = ActiveSheet.Index + 1 To
> > Worksheets(Worksheet.Count).Index
> > Worksheets(Ndx).Range("A1").Value = 123
> > Next Ndx
> >
> > Call ForNextLoop
> >
> >
> >
> > End Sub
> >
> > This is the error:
> >
> > Run-Time Error '424' Expected an Object
> >
> > I get this on the following line..
> >
> > For Ndx = ActiveSheet.Index + 1 To
> > Worksheets(Worksheet.Count).Index
> >
> >
> >
> >
> >
> >
> > "Michael from Austin" wrote:
> >
> >> I have a macro that I want to have also run on the next
> >> worksheet and then
> >> the next until there are no more worksheet. The worksheets
> >> have names. I do
> >> not want it to have to look for the name and then open it. I
> >> just want it to
> >> go to the next one and then the next until the end... Can
> >> this be done. Any
> >> help would be appreceited.....
> >> --
> >> Regards,
> >> Michael
>
>
>

[Chip Pearson]

Michael,

In my code, setting a cell value to 123 was just placeholder
example code. You should replace it with whatever code you want.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Michael from Austin"
<MichaelfromAustin@discussions.microsoft.com> wrote in message
news:CB0BE1C9-B5BA-4ECC-AAE7-BF15675BE068@microsoft.com...
> Cool. It doesn't error anymore, but it also does not go to the
> next
> worksheet. All it does now is add the value"123" to A2 on
> worksheet 2 and
> worksheet nth without actually going there?
>
> "Chip Pearson" wrote:
>
>> "Worksheets" must be plural.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Michael from Austin"
>> <MichaelfromAustin@discussions.microsoft.com> wrote in message
>> news:5BE32321-DF12-4208-AD08-863BF572E642@microsoft.com...
>> > Here is the code that I have entered.
>> >
>> > Sub ChangeSheet()
>> >
>> > ' begins mac in sheet 1 cell a2
>> > Dim Ndx As Integer
>> > For Ndx = ActiveSheet.Index + 1 To
>> > Worksheets(Worksheet.Count).Index
>> > Worksheets(Ndx).Range("A1").Value = 123
>> > Next Ndx
>> >
>> > Call ForNextLoop
>> >
>> >
>> >
>> > End Sub
>> >
>> > This is the error:
>> >
>> > Run-Time Error '424' Expected an Object
>> >
>> > I get this on the following line..
>> >
>> > For Ndx = ActiveSheet.Index + 1 To
>> > Worksheets(Worksheet.Count).Index
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Michael from Austin" wrote:
>> >
>> >> I have a macro that I want to have also run on the next
>> >> worksheet and then
>> >> the next until there are no more worksheet. The worksheets
>> >> have names. I do
>> >> not want it to have to look for the name and then open it.
>> >> I
>> >> just want it to
>> >> go to the next one and then the next until the end... Can
>> >> this be done. Any
>> >> help would be appreceited.....
>> >> --
>> >> Regards,
>> >> Michael
>>
>>
>>

[Michael from Austin]

So, I can call a function in the place of "123"?

"Chip Pearson" wrote:

> Michael,
>
> In my code, setting a cell value to 123 was just placeholder
> example code. You should replace it with whatever code you want.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Michael from Austin"
> <MichaelfromAustin@discussions.microsoft.com> wrote in message
> news:CB0BE1C9-B5BA-4ECC-AAE7-BF15675BE068@microsoft.com...
> > Cool. It doesn't error anymore, but it also does not go to the
> > next
> > worksheet. All it does now is add the value"123" to A2 on
> > worksheet 2 and
> > worksheet nth without actually going there?
> >
> > "Chip Pearson" wrote:
> >
> >> "Worksheets" must be plural.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Michael from Austin"
> >> <MichaelfromAustin@discussions.microsoft.com> wrote in message
> >> news:5BE32321-DF12-4208-AD08-863BF572E642@microsoft.com...
> >> > Here is the code that I have entered.
> >> >
> >> > Sub ChangeSheet()
> >> >
> >> > ' begins mac in sheet 1 cell a2
> >> > Dim Ndx As Integer
> >> > For Ndx = ActiveSheet.Index + 1 To
> >> > Worksheets(Worksheet.Count).Index
> >> > Worksheets(Ndx).Range("A1").Value = 123
> >> > Next Ndx
> >> >
> >> > Call ForNextLoop
> >> >
> >> >
> >> >
> >> > End Sub
> >> >
> >> > This is the error:
> >> >
> >> > Run-Time Error '424' Expected an Object
> >> >
> >> > I get this on the following line..
> >> >
> >> > For Ndx = ActiveSheet.Index + 1 To
> >> > Worksheets(Worksheet.Count).Index
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "Michael from Austin" wrote:
> >> >
> >> >> I have a macro that I want to have also run on the next
> >> >> worksheet and then
> >> >> the next until there are no more worksheet. The worksheets
> >> >> have names. I do
> >> >> not want it to have to look for the name and then open it.
> >> >> I
> >> >> just want it to
> >> >> go to the next one and then the next until the end... Can
> >> >> this be done. Any
> >> >> help would be appreceited.....
> >> >> --
> >> >> Regards,
> >> >> Michael
> >>
> >>
> >>
>
>
>