Auto generate sheets by different values of a cell.
 

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.

Auto generate sheets by different values of a cell.
 

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.

Auto generate sheets by different values of a cell.
 

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

Auto generate sheets by different values of a cell.
 

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

Auto generate sheets by different values of a cell.
 

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.