|
Go To Next Worksheet in Loop
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 |
Go To Next Worksheet in Loop
No need to select
For i = ActiveSheet.Index To Worksheets.Count If UCase(Range("B2")) = "DISTRIBUTOR:" Then FUNCTION1 Next i -- Don Guillett SalesAid Software wrote in message ups.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 |
Go To Next Worksheet in Loop
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 wrote in message ups.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 |
Go To Next Worksheet in Loop
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 "Don Guillett" wrote in message ... 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 wrote in message ups.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 wrote in message ups.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 |
Go To Next Worksheet in Loop
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 "Don Guillett" wrote in message ... 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 wrote in message ups.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 wrote in message ups.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 |
Go To Next Worksheet in Loop
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 wrote in message ups.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 "Don Guillett" wrote in message ... 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 wrote in message ups.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 wrote in message ups.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 |
Go To Next Worksheet in Loop
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 wrote in message ups.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 "Don Guillett" wrote in message ... 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 wrote in message ups.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 wrote in message ups.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 |
Go To Next Worksheet in Loop
You should NOT have to select. Perhaps a re-write of whatever function1 is.
-- Don Guillett SalesAid Software wrote in message ps.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 wrote in message ups.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 "Don Guillett" wrote in message ... 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 wrote in message ups.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 wrote in message ups.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 |
| All times are GMT +1. The time now is 04:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com