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
"Rich" <(E-Mail Removed)> wrote in message
news:5BE3D0F2-B1A9-4236-A283-(E-Mail Removed)...
> 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.
|
Similar Threads
