For Next Loop to Make A4 in Each Sheet = Name of Each Tab

[ryguy7272]

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

 

[ryguy7272]

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

 

[Jim Cone]

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" <[email protected]>
wrote in message
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

 

[Barb Reinhardt]

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]

Woo-Hoo! That's it. I guess everything can be accomplished with one
If...Then. Thanks for the assistance Barb!

 

[ryguy7272]

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--

 

[Dave Peterson]

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]

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--

 

[Dave Peterson]

But if you had entered:
=date(2007,6,31)

Excel would accept it! (well, as July 1, 2007. But xl would be ok.)

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--