Auto generate sheets by different values of a cell.

  • Thread starter Thread starter Dawn
  • Start date Start date
D

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.
 
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
 
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
 
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
 
Hi Joel, helpful indeed!
Many thanks for the detailed instruction.
For further problems may encount with, will consult with you later.
 
Back
Top