| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
Im trying to run a (somewhat) simple procedure, but not getting the results
I am seeking. I have one sheet named Control Sheet and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named Control Sheet) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but cant get it to do what I want it to do. I keep getting a message that says Next Without For, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
Almost got it working with the code below, but now it inserts "Control Sheet"
on the Control Sheet, which is not what I am after. Also, and worse, it is putting the name of each sheet in A4 and B4 of each sheet. Why??? I am trying to get the tab name to be only in cell A4 of each sheet; B4 should be blank. TIA!!! Sub InsertColumns() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control Sheet" Then sh.Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" End If For xs = 1 To Worksheets.Count If sh.Name < "Control Sheet" Then Worksheets(xs).Range("A4").Value = Worksheets(xs).Name End If Next xs Next sh End Sub -- RyGuy "ryguy7272" wrote: Im trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named Control Sheet and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named Control Sheet) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but cant get it to do what I want it to do. I keep getting a message that says Next Without For, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
The End If should be above Next sh.
Also, you only need one loop and the code belongs in a standard modlule not the the module behind a sheet... Sub ListNames() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < ("control sheet") Then sh.Columns("A:A").Insert Shift:=xlToRight sh.Range("A3").Value = "Tab Name" sh.Range("A4").Value = sh.Name End If Next sh End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "ryguy7272" wrote in message Im trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named Control Sheet and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named Control Sheet) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but cant get it to do what I want it to do. I keep getting a message that says Next Without For, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
Try this:
Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "control sheet" Then sh.Range("A:A").Insert Shift:=xlToRight sh.Range("A3") = sh.Name End If Next sh End Sub HTH, Barb Reinhardt "ryguy7272" wrote: Almost got it working with the code below, but now it inserts "Control Sheet" on the Control Sheet, which is not what I am after. Also, and worse, it is putting the name of each sheet in A4 and B4 of each sheet. Why??? I am trying to get the tab name to be only in cell A4 of each sheet; B4 should be blank. TIA!!! Sub InsertColumns() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control Sheet" Then sh.Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" End If For xs = 1 To Worksheets.Count If sh.Name < "Control Sheet" Then Worksheets(xs).Range("A4").Value = Worksheets(xs).Name End If Next xs Next sh End Sub -- RyGuy "ryguy7272" wrote: Im trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named Control Sheet and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named Control Sheet) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but cant get it to do what I want it to do. I keep getting a message that says Next Without For, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
Woo-Hoo! That's it. I guess everything can be accomplished with one
If...Then. Thanks for the assistance Barb! -- RyGuy "Barb Reinhardt" wrote: Try this: Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "control sheet" Then sh.Range("A:A").Insert Shift:=xlToRight sh.Range("A3") = sh.Name End If Next sh End Sub HTH, Barb Reinhardt "ryguy7272" wrote: Almost got it working with the code below, but now it inserts "Control Sheet" on the Control Sheet, which is not what I am after. Also, and worse, it is putting the name of each sheet in A4 and B4 of each sheet. Why??? I am trying to get the tab name to be only in cell A4 of each sheet; B4 should be blank. TIA!!! Sub InsertColumns() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control Sheet" Then sh.Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" End If For xs = 1 To Worksheets.Count If sh.Name < "Control Sheet" Then Worksheets(xs).Range("A4").Value = Worksheets(xs).Name End If Next xs Next sh End Sub -- RyGuy "ryguy7272" wrote: Im trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named Control Sheet and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named Control Sheet) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but cant get it to do what I want it to do. I keep getting a message that says Next Without For, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
Hummm, I just noticed some bizarre behavior. Excel recognized all the days
of the month correctly, such as 6/1, 6/2, 6/3, etc. However, when it gets to 6/31, it thinks this is 6/1/1931. That is so weird! Can anyone explain this? Regards, Ryan-- -- RyGuy "Barb Reinhardt" wrote: Try this: Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "control sheet" Then sh.Range("A:A").Insert Shift:=xlToRight sh.Range("A3") = sh.Name End If Next sh End Sub HTH, Barb Reinhardt "ryguy7272" wrote: Almost got it working with the code below, but now it inserts "Control Sheet" on the Control Sheet, which is not what I am after. Also, and worse, it is putting the name of each sheet in A4 and B4 of each sheet. Why??? I am trying to get the tab name to be only in cell A4 of each sheet; B4 should be blank. TIA!!! Sub InsertColumns() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control Sheet" Then sh.Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" End If For xs = 1 To Worksheets.Count If sh.Name < "Control Sheet" Then Worksheets(xs).Range("A4").Value = Worksheets(xs).Name End If Next xs Next sh End Sub -- RyGuy "ryguy7272" wrote: Im trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named Control Sheet and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named Control Sheet) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but cant get it to do what I want it to do. I keep getting a message that says Next Without For, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
Duh-Oh! You're totally right! My brain shut down some time ago. I'm outta
here...that's enough work for today. Thanks Dave!! Ryan-- -- RyGuy "Dave Peterson" wrote: Excel sees 6/1, 6/2, ..., 6/31 as a date. I'm guessing that most people would mean June 1st when they type 6/1 (assuming mdy order). But 6/31 can't be June 31st (according to most calendars <bg). So it guesses that you're entering a year (1931). And parses your entry as June 1, 1931. ryguy7272 wrote: Hummm, I just noticed some bizarre behavior. Excel recognized all the days of the month correctly, such as 6/1, 6/2, 6/3, etc. However, when it gets to 6/31, it thinks this is 6/1/1931. That is so weird! Can anyone explain this? Regards, Ryan-- -- RyGuy "Barb Reinhardt" wrote: Try this: Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "control sheet" Then sh.Range("A:A").Insert Shift:=xlToRight sh.Range("A3") = sh.Name End If Next sh End Sub HTH, Barb Reinhardt "ryguy7272" wrote: Almost got it working with the code below, but now it inserts "Control Sheet" on the Control Sheet, which is not what I am after. Also, and worse, it is putting the name of each sheet in A4 and B4 of each sheet. Why??? I am trying to get the tab name to be only in cell A4 of each sheet; B4 should be blank. TIA!!! Sub InsertColumns() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control Sheet" Then sh.Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" End If For xs = 1 To Worksheets.Count If sh.Name < "Control Sheet" Then Worksheets(xs).Range("A4").Value = Worksheets(xs).Name End If Next xs Next sh End Sub -- RyGuy "ryguy7272" wrote: Iâm trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named âœControl Sheetâ and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named âœControl Sheetâ) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but canât get it to do what I want it to do. I keep getting a message that says â˜Next Without Forâ, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Next Loop to Make A4 in Each Sheet = Name of Each Tab
But if you had entered:
=date(2007,6,31) Excel would accept it! (well, as July 1, 2007. But xl would be ok.) ryguy7272 wrote: Duh-Oh! You're totally right! My brain shut down some time ago. I'm outta here...that's enough work for today. Thanks Dave!! Ryan-- -- RyGuy "Dave Peterson" wrote: Excel sees 6/1, 6/2, ..., 6/31 as a date. I'm guessing that most people would mean June 1st when they type 6/1 (assuming mdy order). But 6/31 can't be June 31st (according to most calendars <bg). So it guesses that you're entering a year (1931). And parses your entry as June 1, 1931. ryguy7272 wrote: Hummm, I just noticed some bizarre behavior. Excel recognized all the days of the month correctly, such as 6/1, 6/2, 6/3, etc. However, when it gets to 6/31, it thinks this is 6/1/1931. That is so weird! Can anyone explain this? Regards, Ryan-- -- RyGuy "Barb Reinhardt" wrote: Try this: Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "control sheet" Then sh.Range("A:A").Insert Shift:=xlToRight sh.Range("A3") = sh.Name End If Next sh End Sub HTH, Barb Reinhardt "ryguy7272" wrote: Almost got it working with the code below, but now it inserts "Control Sheet" on the Control Sheet, which is not what I am after. Also, and worse, it is putting the name of each sheet in A4 and B4 of each sheet. Why??? I am trying to get the tab name to be only in cell A4 of each sheet; B4 should be blank. TIA!!! Sub InsertColumns() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If sh.Name < "Control Sheet" Then sh.Activate Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" End If For xs = 1 To Worksheets.Count If sh.Name < "Control Sheet" Then Worksheets(xs).Range("A4").Value = Worksheets(xs).Name End If Next xs Next sh End Sub -- RyGuy "ryguy7272" wrote: Iâm trying to run a (somewhat) simple procedure, but not getting the results I am seeking. I have one sheet named âœControl Sheetâ and I am running the macro from here. I am trying to insert a column into each of the other sheets in the workbook (all sheets not named âœControl Sheetâ) and then get the name of each sheet in cell A4 of each sheet. Sounds simple enough, right. I played with the code below a little, but canât get it to do what I want it to do. I keep getting a message that says â˜Next Without Forâ, but I thought I had the For and Next structured properly. Argh!! Any help would be MUCH appreciated. TIA! Sub ListNames() Dim sh As Worksheet Dim xs As Integer For Each sh In ActiveWorkbook.Worksheets If LCase(sh.Name) < "Control Sheet" Then Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A3").Select ActiveCell.FormulaR1C1 = "Tab Name" For xs = 1 To Worksheets.Count Worksheets(xs).Range("A4").Value = Worksheets(xs).Name Next xs Next sh End If Next End Sub -- RyGuy -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't make loop macro work - help? | Excel Programming | |||
| How can I make For-Next loop with date? | Excel Programming | |||
| How can I make For-Next loop with date? | Excel Programming | |||
| I want to make one loop | Excel Programming | |||
| How to Make a Loop count by 1% not 1 | Excel Programming | |||
Linear Mode
