Create a Dynamic Master List from 10 different sheets.

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.