Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete the same columns across a workseet | Excel Discussion (Misc queries) | |||
Workseet shrink when printing. | Excel Discussion (Misc queries) | |||
Workseet Colaberation | Excel Worksheet Functions | |||
Workseet linking map | Excel Discussion (Misc queries) | |||
Put a Worksheet in a workseet? | Excel Worksheet Functions |