What was that website again?

[Audio_freak]

A while ago I had a question, and was referred to a website where someone
had all kinds of excel documents with macro's in them, as examples.

Searching in the web just generates too much results, I can't find it
anymore.

Here's what I am looking for and once saw on that site:

A document with
- unsorted data in the first sheet, one column of which contains various
city names repeatedly coming back.
- a list of the city names in the second sheet
- a macro which creates separate sheets for each of the city names and puts
all the unsorted data into the relevant sheet.

I'd be very happy finding that again, now that I need it...

Thanks!

Peter

 

[Debra Dalgleish]

That sounds like my "Update Sheets from Master' sample:

http://www.contextures.com/excelfiles.html

Under the heading 'Filters'

 

[Audio_freak]

http://www.contextures.com/excelfiles.html

it isn't the site I was aiming for, but it contains one file which is
almost it. It is called "Extract Items with Formulas" , but this one
doesn't work properly (at least on my PC), plus I believe it tries to
create new files whereas I am looking to create additional sheets....

thanks for the tip anyway...

 

[Ken Wright]

Does this help:-

Sub ShowPagesLikePivotTable()

Dim SrcSht As Worksheet
Dim SrcShtlrow As Long
Dim SrcShtlCol As Long
Dim FiltRnglrow As Long
Dim FiltRng As Range
Dim SrcRng1 As Range
Dim SrcRng2 As Range
Dim NewSht As Worksheet
Dim NumShts As Long
Dim Cel As Range

Application.ScreenUpdating = False

Set SrcSht = ActiveSheet
SrcSht.Name = "Source Data Sheet"
SrcShtlrow = SrcSht.Cells(Rows.Count, "A").End(xlUp).Row

SrcShtlCol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

With SrcSht
Set SrcRng1 = .Range(Cells(1, "A"), Cells(SrcShtlrow, "A"))
Set SrcRng2 = .Range(Cells(1, "A"), Cells(SrcShtlrow, SrcShtlCol))

SrcRng1.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("IV1"), Unique:=True

FiltRnglrow = .Cells(Rows.Count, "IV").End(xlUp).Row
Set FiltRng = .Range(Cells(2, "IV"), Cells(FiltRnglrow, "IV"))
End With

FiltRng.Sort Key1:=Range("IV2"), Order1:=xlAscending, Header:=xlGuess

For Each Cel In FiltRng
Set NewSht = Worksheets.Add
NewSht.Name = Cel.Value
NumShts = Sheets.Count
NewSht.Move After:=Sheets(NumShts)

With SrcRng2
.AutoFilter Field:=1, Criteria1:=Cel.Value
.SpecialCells(xlCellTypeVisible).Copy NewSht.Range("A1")
End With
Application.StatusBar = "Generated " & Cel.Row & " of " _
& FiltRnglrow - 1 & " Sheets"
Next Cel

SrcRng1.AutoFilter

With SrcSht
.Activate
.Range("IV:IV").Delete
.Range("A1").Select
End With

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

 

[Gord Dibben]

Googling on your name, I found this thread about making new workbooks from
sheets.

May be the one you are referring to, although I don't see any URL to a website
other than Ron de Bruin's.

http://snipurl.com/6bgg

Gord Dibben Excel MVP

 

[Audio_freak]

Thanks, great tool to find history. I traced a little further in
history, but didn't find it. I must have read the page while reading
other messages.

Thanks again.

P

 

[Audio_freak]

I've been thinking about using a pivot table. No experience yet. I will
investigate it. Thanks for the code.

P.

 

[David McRitchie]

I think of Googling as meaning a web search,
and probably Google as well, I think I would say web search, or groups search,
but I think it was clear everyone was using the newsgroup search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Take a look at my page on Formulas
Show FORMULA or FORMAT of another cell
http://www.mvps.org/dmcritchie/excel/formula.htmI

for a list of formulas see the related area at the bottom of the page.

I find the use GetFormula and it's variations much more practical
than a list of formulas, but for a list of formulas you are probably
referring to John Walkenbach's Creating a List of Formulas (Tip 37)
http://j-walk.com/ss/excel/tips/tip37.htm

 

[Ron de Bruin]

Maybe this one ?

http://www.rondebruin.nl/copy5.htm

 

[Audio_freak]

that must be it.

thanks !