Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to create sheets and tab names from first colum of data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to create sheets and tab names from first colum of data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to create sheets and tab names from first colum of data | Excel Programming | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
Keeping a sum colum correct after inserting a colum of data in fro | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) |