simple question

  • Thread starter Thread starter Patrick Knott
  • Start date Start date
P

Patrick Knott

Is there anyway to create a workbook with sheet names
from a list in another workbook? Ex: I have a list of
names in one workbook. I want to create anohter workbook
in which each person has a name.
It's a pretty long list, so naming each sheet manually
would be tiresome at best.

Thanks.

Patrick
 
Make that third sentence read: "another workbook in which
each person has a sheet."

That should help.
 
Didn't work--brought up debugger with

ActiveSheet.Name = Cel.Value

line highlighted.

As I am not a VB user, don't know how to fix....
-----Original Message-----
Create a new workbook with one sheet in it, and have the
Click to expand...
list of names reside in Col A, starting
A1, and then run the following macro.

Sub InsSheets()

Dim x As Long
Dim c As Long
Dim lastrw As Long
Dim Rng As Range
Dim Cel As Range

Application.ScreenUpdating = False

lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))

For x = 1 To Rng.Rows.Count Step 1
For Each Cel In Rng.Rows(x)
c = ActiveWorkbook.Sheets.Count
Sheets.Add After:=Sheets(c)
ActiveSheet.Name = Cel.Value
Next Cel
Next x

Application.ScreenUpdating = True

End Sub

There is no error checking in there, so if you tried it
Click to expand...
on a workbook that already had sheetnames
 
I found the problem. Took a while, as it was really my
first time tinkering with macros in any serious way.

Thanks tons for the help.
 
Back
Top