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.

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).Entir eRow.Copy _
Destination:=newsht.Cells
If .FilterMode = True Then
.ShowAllData
End If
End If
Next city
End With
"Dawn" wrote:

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.

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 dont know where to start. Pls help me, thanks.
Dawn

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).Entir eRow.Copy _
Destination:=newsht.Cells
If .FilterMode = True Then
.ShowAllData
End If
End If
Next city
End With
End Sub

"Dawn" wrote:

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 dont know where to start. Pls help me, thanks.
Dawn

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

"Joel" wrote:

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).Entir eRow.Copy _
Destination:=newsht.Cells
If .FilterMode = True Then
.ShowAllData
End If
End If
Next city
End With
End Sub

"Dawn" wrote:

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 dont know where to start. Pls help me, thanks.
Dawn

Joel,

I have a similar project. However, I have multiple columns that will be linked in the second sheet. I am not sure if .Columns(FiltCol).AdvancedFilter can be extended to add more fields or should I add another line there for each column.

The two spreadsheets are attached.

The highlighted columns in the data sheet(sheet1) are variable elements in sheet 2. The sheet 2 needs to be generated for each member listed in Sheet1 with the variable data items as highlighted in yellow in sheet1.


Please Help.

Thanks. Appreciate your help in this matter.