Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
create new sheet with zip code as name
Am importing some sales records that I am trying to keep track of by zip
codes. Want to create a new sheet if one doesn't already exist from the records (G1 thru ???) and use the zip for the name of the sheet. Am a novice, so please be specific with suggested code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
create new sheet with zip code as name
Try this code. Change sheet name to sheet where zipcodes are located.
Sub createsheets() 'set sheet with zipcodes Set MasterSht = Sheets("Sheet1") With MasterSht RowCount = 1 'loop until blank cell is found Do While .Range("G" & RowCount) < "" zipcode = .Range("G" & RowCount) 'check each sheet for zipcode name Found = False For Each sht In Sheets If sht.Name = zipcode Then Found = True Exit For End If Next sht 'if zipcode not found If Found = False Then Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = zipcode End If RowCount = RowCount + 1 Loop End With End Sub "Rich" wrote: Am importing some sales records that I am trying to keep track of by zip codes. Want to create a new sheet if one doesn't already exist from the records (G1 thru ???) and use the zip for the name of the sheet. Am a novice, so please be specific with suggested code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
create new sheet with zip code as name
Thanks. But I told you wrong. Col N not G. Works fine on all columns BUT N.
Have tried changing format cells for that column, but nothing seems to work. Get same error on compile. Help! "Joel" wrote: Try this code. Change sheet name to sheet where zipcodes are located. Sub createsheets() 'set sheet with zipcodes Set MasterSht = Sheets("Sheet1") With MasterSht RowCount = 1 'loop until blank cell is found Do While .Range("G" & RowCount) < "" zipcode = .Range("G" & RowCount) 'check each sheet for zipcode name Found = False For Each sht In Sheets If sht.Name = zipcode Then Found = True Exit For End If Next sht 'if zipcode not found If Found = False Then Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = zipcode End If RowCount = RowCount + 1 Loop End With End Sub "Rich" wrote: Am importing some sales records that I am trying to keep track of by zip codes. Want to create a new sheet if one doesn't already exist from the records (G1 thru ???) and use the zip for the name of the sheet. Am a novice, so please be specific with suggested code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
create new sheet with zip code as name
Seems to crash second time through on any column. Error 1004 which I think is
trying to write a new record when one already exists (sheet name). When I delete all sheets except master and do it again, works fine. But if I repeat without deleting sheets, crashes. I can't firgure out what is wrong with the code, unless it has to do with the resetting of "sht". "Rich" wrote: Thanks. But I told you wrong. Col N not G. Works fine on all columns BUT N. Have tried changing format cells for that column, but nothing seems to work. Get same error on compile. Help! "Joel" wrote: Try this code. Change sheet name to sheet where zipcodes are located. Sub createsheets() 'set sheet with zipcodes Set MasterSht = Sheets("Sheet1") With MasterSht RowCount = 1 'loop until blank cell is found Do While .Range("G" & RowCount) < "" zipcode = .Range("G" & RowCount) 'check each sheet for zipcode name Found = False For Each sht In Sheets If sht.Name = zipcode Then Found = True Exit For End If Next sht 'if zipcode not found If Found = False Then Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = zipcode End If RowCount = RowCount + 1 Loop End With End Sub "Rich" wrote: Am importing some sales records that I am trying to keep track of by zip codes. Want to create a new sheet if one doesn't already exist from the records (G1 thru ???) and use the zip for the name of the sheet. Am a novice, so please be specific with suggested code. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
create new sheet with zip code as name
Rich,
To fix your problems, use this macro, written assuming you have headers in row 1, and your sheet is name Master Sheet Sub MakeZipCodeSheets() Dim mySht As Worksheet Dim myArea As Range Dim myCell As Range Dim myName As String Set mySht = Worksheets("Master Sheet") Set myArea = Intersect(mySht.UsedRange, _ mySht.Range("N2:N" & Rows.Count)) On Error GoTo NoSheet For Each myCell In myArea myName = Worksheets(myCell.Value).Name GoTo SheetExists: NoSheet: Set mySht = Worksheets.Add(befo=Worksheets(1)) mySht.Name = myCell.Value Resume SheetExists: Next myCell End Sub HTH, Bernie MS Excel MVP "Rich" wrote in message ... Seems to crash second time through on any column. Error 1004 which I think is trying to write a new record when one already exists (sheet name). When I delete all sheets except master and do it again, works fine. But if I repeat without deleting sheets, crashes. I can't firgure out what is wrong with the code, unless it has to do with the resetting of "sht". "Rich" wrote: Thanks. But I told you wrong. Col N not G. Works fine on all columns BUT N. Have tried changing format cells for that column, but nothing seems to work. Get same error on compile. Help! "Joel" wrote: Try this code. Change sheet name to sheet where zipcodes are located. Sub createsheets() 'set sheet with zipcodes Set MasterSht = Sheets("Sheet1") With MasterSht RowCount = 1 'loop until blank cell is found Do While .Range("G" & RowCount) < "" zipcode = .Range("G" & RowCount) 'check each sheet for zipcode name Found = False For Each sht In Sheets If sht.Name = zipcode Then Found = True Exit For End If Next sht 'if zipcode not found If Found = False Then Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = zipcode End If RowCount = RowCount + 1 Loop End With End Sub "Rich" wrote: Am importing some sales records that I am trying to keep track of by zip codes. Want to create a new sheet if one doesn't already exist from the records (G1 thru ???) and use the zip for the name of the sheet. Am a novice, so please be specific with suggested code. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
create new sheet with zip code as name
My code just need a declararation statement
Dim zipcode As String Because zicode are numbers and sheet names are strings zipcode = sht.name where 10001 < "10001" adding the declaration make zipcode and string and then the sheet name will equal the value in the worksheet. "Rich" wrote: Seems to crash second time through on any column. Error 1004 which I think is trying to write a new record when one already exists (sheet name). When I delete all sheets except master and do it again, works fine. But if I repeat without deleting sheets, crashes. I can't firgure out what is wrong with the code, unless it has to do with the resetting of "sht". "Rich" wrote: Thanks. But I told you wrong. Col N not G. Works fine on all columns BUT N. Have tried changing format cells for that column, but nothing seems to work. Get same error on compile. Help! "Joel" wrote: Try this code. Change sheet name to sheet where zipcodes are located. Sub createsheets() 'set sheet with zipcodes Set MasterSht = Sheets("Sheet1") With MasterSht RowCount = 1 'loop until blank cell is found Do While .Range("G" & RowCount) < "" zipcode = .Range("G" & RowCount) 'check each sheet for zipcode name Found = False For Each sht In Sheets If sht.Name = zipcode Then Found = True Exit For End If Next sht 'if zipcode not found If Found = False Then Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = zipcode End If RowCount = RowCount + 1 Loop End With End Sub "Rich" wrote: Am importing some sales records that I am trying to keep track of by zip codes. Want to create a new sheet if one doesn't already exist from the records (G1 thru ???) and use the zip for the name of the sheet. Am a novice, so please be specific with suggested code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use VBA to create new sheet with event handlers in sheet's code | Excel Programming | |||
Create Bar code Sheet w/lookups, index/match | Excel Worksheet Functions | |||
Code to create atbs in excel sheet | Excel Discussion (Misc queries) | |||
Code to create tabs in single excel sheet | Excel Discussion (Misc queries) | |||
Code to create and name a sheet | Excel Programming |