Create a Dynamic Master List from 10 different sheets.

Hi all,

Thanks for all your help so far helping me create a automated DJ Playlist for my Wedding Gigs.

I now have a workbook with about 10 Sheets , each Sheet is a specific Genre i.e. 80's, 70's, House, Top40, Rnb etc...

Clients can now go into each Sheet, select from about 300 tracks by ticking (marking cell with X) and a custom list of their music choices will appear in column F1 (on the same sheet)
So now I have 10 sheets of song selections for each Genre (btw data will end up in columns "F1" in all 10 sheets)

Can anyone help me with a formula so all the data in F1 on all 10 sheets would be copy to a new Sheet...say Sheet 11 - from A1 downward

As you may have guess it would be a master list of of the song choices and would be dynamic so as they add to F1 in various sheets it would update master sheet automatically.

Hope this make sense any help appreciated

Hello DjShane,

Perhaps the following code assigned to a button on a "Summary" sheet should do the task for you:-

Sub SummariseSongs()

Application.ScreenUpdating = False
        
        Dim ws As Worksheet
        Dim lr As Long

lr = Range("F" & Rows.Count).End(xlUp).Row
Sheets("Summary").UsedRange.Offset(1).ClearContents

        For Each ws In Worksheets
        ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp)).Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)
        ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp)).Delete
 Next ws

Sheets("Summary").Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've attached my test work book for you to play with. In it, I have five sheets plus a Summary sheet. The code will work on ten sheets without any problems. Click on the "Summarise" button to see it work.

The code also deletes the Column F entries from each sheet once transferred to the "Summary" sheet and the "Summary" sheet will clear old data once new data is transferred over (or we could create a separate button just to clear the "Summary" sheet if you like).

I hope that this helps.

Cheerio,
vcoolio.

Thanks mate, the coding looks complicated... but it works perfectly. Again thanks vcoolio

Hi DjShane,

You're welcome.

Actually, its fairly basic. But as long as it does the job for you...............

Glad that I was able to help.

Cheerio,
vcoolio.