create new sheet with zip code as name

[Rich]

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.

 

[Joel]

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]

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!

 

[Rich]

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".

 

[Bernie Deitrick]

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(before:=Worksheets(1))
mySht.Name = myCell.Value
Resume
SheetExists:
Next myCell

End Sub


HTH,
Bernie
MS Excel MVP

 

[Joel]

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.