|
Make Macro go to Next Workseet
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 |
Make Macro go to Next Workseet
for each sh in Activeworkbook.Worksheets
might be what you are looking for. -- Regards, Tom Ogilvy "Michael from Austin" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
I'm still getting the same error...
"Chip Pearson" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
I think it's time for you to paste the procedure that you're trying.
Michael from Austin wrote: I'm still getting the same error... "Chip Pearson" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 -- Dave Peterson |
Make Macro go to Next Workseet
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 |
Make Macro go to Next Workseet
"Worksheets" must be plural.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Michael from Austin" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
Probably.
It may be time again to post what you really want to do. Michael from Austin wrote: 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" wrote in message ... 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" wrote in message ... 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 -- Dave Peterson |
Make Macro go to Next Workseet
Yes, you can call a function from within the loop.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Michael from Austin" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Make Macro go to Next Workseet
Thanks fro all your help Chip. I figured it out. This is what I wanted to do..
Sub ChangeSheet() Dim Ndx As Integer If ActiveSheet.Index = Worksheets(Worksheets.Count).Index Then End Else For Ndx = ActiveSheet.Index + 1 To Worksheets(Worksheets.Count).Index Sheets(Ndx).Select Call ForNextLoop Next Ndx End If End Sub "Chip Pearson" wrote: Yes, you can call a function from within the loop. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Michael from Austin" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com