Auto generate sheets by different values of a cell.

[Dawn]

I have a sheet in excel ,with the following fields
Name, gender…,city.
I want to use excel to auto generate sheets according to the value of the
field city.
For example :
If the city only has two values :LA, NY
Excel will generate two new sheets ,â€LA†with the all the records in
original sheet but the city value=LA; â€NY†with the all the records in
original sheet but the city value=NY;
How can I realize it.
Many thanks.

 

[Joel]

With Sheets("sheet1")
.Columns("F").AdvancedFilter _
Unique:=True, Action:=xlFilterInPlace
Set UniqueCities = .Columns("F").SpecialCells( _
Type:=xlCellTypeVisible, _
Value:=xlTextValues)

For Each city In UniqueCities
If city <> "" Then
.Columns("F").AutoFilter _
field:=1, _
Criteria1:=city

Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
newsht.Name = city
.Cells.SpecialCells(Type:=xlCellTypeVisible).EntireRow.Copy _
Destination:=newsht.Cells
If .FilterMode = True Then
.ShowAllData
End If
End If
Next city
End With

 

[Dawn]

Dear Joel,
I am a basic learner of excel.
Is the above VBA or Marco? Can you take some trouble as to tell me how do I
put these sentences in excel in details.
I am absolutely don’t know where to start. Pls help me, thanks.
Dawn

 

[Joel]

VBA is the visual basic Application that runs macros. You can have either
subroutines (Sub) or Functions, both are reffered to as macros. This code is
a subroutine.


1) Go to the VBA envirnoment Press Alt F11.
2) VBA Menu - Insert - Module
3) Copy macro from the word SUB to END SUB and paste into module sheet
4) You can run the macro 2 different ways.
a) from VBA press F5 (or menu Run - Run)
b) From Worksheet Tools - Macro - Macro - MakeSheets


You didn't specify the worksheet name or the column letter where the citiy
names are located. You may need to change these two lines

1) FiltCol = "F" which is the column where the cities are located
2) With Sheets("sheet1") which is the sheet name where you original data
is located.

Sub MakeSheets()

FiltCol = "F" '<= change if necessary

With Sheets("sheet1") '<= change if necessary

.Columns(FiltCol).AdvancedFilter _
Unique:=True, Action:=xlFilterInPlace
Set UniqueCities = .Columns("F").SpecialCells( _
Type:=xlCellTypeVisible, _
Value:=xlTextValues)

For Each city In UniqueCities
If city <> "" Then
.Columns(FiltCol).AutoFilter _
field:=1, _
Criteria1:=city

Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
newsht.Name = city
.Cells.SpecialCells(Type:=xlCellTypeVisible).EntireRow.Copy _
Destination:=newsht.Cells
If .FilterMode = True Then
.ShowAllData
End If
End If
Next city
End With
End Sub

 

[Dawn]

Hi Joel, helpful indeed!
Many thanks for the detailed instruction.
For further problems may encount with, will consult with you later.