create new sheet with zip code as name

R

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

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
 
R

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!
 
R

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

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top