trying to create sheets and tab names from first colum of data
Thanks, but I am getting a subfunction not defined on workbook?
What am I missing?
Thanks
--
Nelson
"Barb Reinhardt" wrote:
Try this
Option Explicit
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim myWS As Excel.Worksheet
Dim WS As Excel.Worksheet
Dim myWB As Excel.Workbook
Set myWB = ThisWorkbook
Set myWS = Worksheet("RawData")
Set MyRange = myWS.Range("L:L")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Set myWS = Nothing
On Error Resume Next
Set myWS = myWB.Worksheets(MyCell.Value)
On Error GoTo 0
If myWS Is Nothing Then
Set myWS = Worksheet.Add(After:=Worksheet(Sheets.Count)) 'creates a
new worksheet
myWS.Name = MyCell.Value ' renames the new worksheet
End If
Next MyCell
End Sub
HTH,
Barb Reinhardt
"Nelson" wrote:
I have a DB result sheet, now I am trying to create worksheets for each
individual customer number in the first column of my datasheet and give the
tab of that worksheet the customer number.
I am using this but it doesnt seem to be working
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("RawData").Range("RawData!L:L")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub
what I am looking for it to do is go through my rawdata in column A and
create a worksheet (giving the tab the name of the cusotmer number), I also
wanted this to skip existing customer numbers where a sheet already exists
and move on till its done.
Any thoughts?
Thanks in advance
--
Nelson
|