Renaming sheets based upon cells

[drsharris]

Can anyone help me! I have been searching the forums for two nights but cannot figure things out!
Basically I have an excel sheet (sheet1), with a list of company names in the B column (nearly 500 names). I would like to use one of those macro things to automatically generate a new sheet for every company name in the B column.

Can this be done and if so how? I have tried a number of code things I have found through various google searches but there have been various problems/errors so can anyone help me?!

 

[jbeaucaire]

CREATE SHEETS
Here's a macro for creating an ordered set of sheets based on a column of strings AND add clickable hyperlinks in an adjacent column, basically a linkable index sheet. There's a sample sheet to try it on:

Create Sheets​

 

[drsharris]

Hi jbeaucaire,

Thanks for your reply.

I tried using the code you supplied and it created the new sheets based upon the A column data which is the individual customer account data. I actually want the sheets to be renamed based upon the B column data which is the customer name so i changed your code to this..


Option Explicit

Sub CreateSheets()
'Author: Jerry Beaucaire
'Date: 7/14/2010
'Summary: Use an index table to create/sort sheets in a workbook with hyperlinks

Dim RNG As Range
Dim c As Range
Application.ScreenUpdating = False

'Set list of sheetnames
Set RNG = ActiveSheet.Range("B2:B" & Rows.Count).SpecialCells(xlConstants)

For Each c In RNG
'test if the sheet exists already
If Not Evaluate("ISREF('" & c.Text & "'!B1)") Then
'Create it if it doesn't exist
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Text
Else
'Move it to the correct spot if it already exists
Sheets(c.Text).Move After:=Sheets(Sheets.Count)
End If

'add hyperlink on new sheet to get back to INDEX page
Sheets(c.Text).Range("B1").Formula = "=HYPERLINK(""#Index!B1"",""Home"")"

'add hyperlink on INDEX to new sheet
c.Offset(, 1).FormulaR1C1 = "=HYPERLINK(""#'"" & RC[-1] & ""'!B1"", ""Link"")"
Next c

RNG.Parent.Activate
Application.ScreenUpdating = True
End Sub


However, I think i must have done something wrong because after I hit run macro i get a message which says run time error 1004 application defined or object defined error

Can you give me any advice! I am a complete novice and don't really have a clue what i am doing!! Is is something to do with the data in the B column perhaps not being suitable as sheet names? i.e. too many characters?!?

 

[jbeaucaire]

When you get an error like that, DEBUG and examine the line of code that it highlights. Hover your mouse over any variables to see what the current value of those variables are....

Looking at the code and that error, I'm going to guess maybe you don't have a sheet in your workbook called Index...

'add hyperlink on new sheet to get back to INDEX page
Sheets(c.Text).Range("B1").Formula = "=HYPERLINK(""#Index!B1"",""Home"")"

The macro is creating the hyperlink "home" in cell B1 on each created sheet. Name that sheet INDEX or edit the code.

If this isn't the line of code highlighted by the debugger. which is?

 

[drsharris]

Hi

I renamed the sheet Index as suggested - still didn't work so debugged and three lines get highlighted in yello-

Option Explicit
Sub CreateSheets()
'Author: Jerry Beaucaire
'Date: 7/14/2010
'Summary: Use an index table to create/sort sheets in a workbook with hyperlinks
Dim RNG As Range
Dim c As Range
Application.ScreenUpdating = False
'Set list of sheetnames
Set RNG = ActiveSheet.Range("B2:B" & Rows.Count).SpecialCells(xlConstants)
For Each c In RNG
'test if the sheet exists already
If Not Evaluate("ISREF('" & c.Text & "'!B1)") Then
'Create it if it doesn't exist
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Text
Else
'Move it to the correct spot if it already exists
Sheets(c.Text).Move After:=Sheets(Sheets.Count)
End If
'add hyperlink on new sheet to get back to INDEX page
Sheets(c.Text).Range("B1").Formula = "=HYPERLINK(""#Index!B1"",""Home"")"
'add hyperlink on INDEX to new sheet
c.Offset(, 1).FormulaR1C1 = "=HYPERLINK(""#'"" & RC[-1] & ""'!B1"", ""Link"")"
Next c
RNG.Parent.Activate
Application.ScreenUpdating = True
End Sub


When I hover over the first - nothing happens.

The second it says application.screenupdating=true and false=false

On the third it says RNG=nothing and rows.count=655336 and xlconstants=2


Any ideas!?

 

[jbeaucaire]

There is no good reason I can think of the debugger would highlight the TITLE of the macro?? How odd is that?

1) open a new module
2) at the top manually type in Sub CreateSheets and press Enter.
3) Excel should add the stuff in red, so it now looks like this:

Sub CreateSheets()

End Sub

4) now paste in the rest of the code that goes between.


If this mystery persists, you can send me the file to look at, something unobvious may be clear to a second set of eyes. Use the link in my signature to send me the file through my Excel macros site.