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

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

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

Under the heading 'Filters'

Audio_freak wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish wrote in news:40A1439F.1000109
@contexturesXSPAM.com:

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... :)

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Audio_freak" wrote in message
.2...
Debra Dalgleish wrote in news:40A1439F.1000109
@contexturesXSPAM.com:

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... :)


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004

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

On 11 May 2004 20:53:51 GMT, "Audio_freak" wrote:



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

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










Gord Dibben <gorddibbATshawDOTca wrote in
:

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

On 11 May 2004 20:53:51 GMT, "Audio_freak"
wrote:



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

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

P.







"Ken Wright" wrote in
:

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

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_gr...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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Audio_freak" wrote in message . 2.2...


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










Gord Dibben <gorddibbATshawDOTca wrote in
:

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

On 11 May 2004 20:53:51 GMT, "Audio_freak"
wrote:



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

Maybe this one ?

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Audio_freak" wrote in message . 2.2...


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










Gord Dibben <gorddibbATshawDOTca wrote in
:

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

On 11 May 2004 20:53:51 GMT, "Audio_freak"
wrote:



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

that must be it.

thanks !




"Ron de Bruin" wrote in news:uxIT9$COEHA.2716
@tk2msftngp13.phx.gbl:

Maybe this one ?

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