RE: Auto generate sheets by different values of a cell.
Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance
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" 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.
.
Relevant Pages
- Re: Mail Merge Field Problem
... In Excel, just make sure the first row has ... Street Address, and City, State, Zipcode. ... Ususally I just tell it entire spreadsheet. ... >> Do you have any hidden columns in the non-working sheets? ...
(microsoft.public.word.mailmerge.fields) - Re: Access help needed! Over my head!
... individual tables or sheets, and have a resulting table showing a list ... query wizard" in Access, but that will only find me the lines of data ... where there is no match between sheet A and B for the "matching field" ... city B, 300 rows for city C, and 500 rows for city D, and there were a ...
(microsoft.public.access.queries) - Access help needed! Over my head!
... individual tables or sheets, and have a resulting table showing a list ... of every row of data where a change occured in one or more cells? ... query wizard" in Access, but that will only find me the lines of data ... city B, 300 rows for city C, and 500 rows for city D, and there were a ...
(microsoft.public.access.queries) - Excel help needed! Over my head!
... list of every row of data where a change occured in one or more cells? ... city B, 300 rows for city C, and 500 rows for city D, and there were a ... sheets, can I run a count to see how many of the 75 rows were city A ... Tab B, for each of City A, B, C, and D (Tab B is a huge database list ...
(microsoft.public.excel) - help needed! excel formulas above my head!
... list of every row of data where a change occured in one or more cells? ... city B, 300 rows for city C, and 500 rows for city D, and there were a ... sheets, can I run a count to see how many of the 75 rows were city A ... Tab B, for each of City A, B, C, and D (Tab B is a huge database list ...
(microsoft.public.excel.programming) |
|
