Help with creating mini worksheets from major workbook

[Dan Wilson]

Good day. I have been asking for a lot of help over the last few days, but this should be the last one for a while. I am running Office 365 (updated) on Windows 10 Home. I have a workbook that will create a worksheet titled "DB" containing a list of almost 5,000 songs in my Music folder. The DB worksheet will contain 8 Columns of data with one of those columns being the Genre of each song. Once the DB has been created, I would like to create additional worksheets in the same workbook dedicated to each Genre of music. This process does not have to be part of the DB creation Macro. The new worksheet will look exactly like the DB with the same formatting and Column Width. What I need is a formula that will search each of the 5,000 entries and populate the new worksheet with just the songs that match the selected Genre. I am fairly good with Macros and hope that this can be resolved by creating a Macro for each Genre type. Once that is done, I can then combine all the Macros into one. Thank you for any help.
Dan Wilson...

 

[Logit]

.

Option Explicit

Sub Create_separate_worksheet()
    Dim c As Range, sh As Worksheet, Ky As Variant
    
    Set sh = Sheets(1)
    Application.ScreenUpdating = False
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next c
        For Each Ky In .Keys
            sh.Range("A1").AutoFilter 1, Ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
            sh.AutoFilter.Range.EntireRow.Copy Range("A1")
        Next Ky
    End With
    Application.ScreenUpdating = True
    sh.ShowAllData
End Sub

 

[Dan Wilson]

Good day Logit. Thank you for responding. I guess I'm not as good at Macros as I thought. I recognize a lot of what you sent, but not enough. Let's try this a different way. The DB worksheet will contain almost 5,000 rows with 8 columns per row.

Column A = Title
B = Artist
C = Year
D = #
E = Length
F = Comments
G = Genre
H = Extra

I want the Macro to search DB for a desired Genre in Column G and then copy the entire contents of the row that has the matching Genre into a designated worksheet titles for that Genre. I will create a Macro to search for each desired Genre, then combine the Macros into one Macro. I'm sorry if I spoke too highly of myself. I am learning. Thank you.
Dan Wilson...

 

[Peter_SSs]

Do you really need a macro?

If you have the FILTER function in your Excel 365 and (if you haven't already) turn the date in 'DB' into a formal table (select any cell in the data then Ctrl+T -> My table has headers)
then in the other sheets you only need one formula in one cell.

Small example

Dan Wilson 2020-07-19 1.xlsm
	A	B	C	D	E	F	G	H
1	Title	Artist	Year	#	Length	Comments	Genre	Extra
2	Title 1	Artist 4	2008	#1	Len 1	Comment 1	a	Extra 1
3	Title 2	Artist 2	1993	#2	Len 2	Comment 2	b	Extra 2
4	Title 3	Artist 1	1989	#3	Len 3	Comment 3	c	Extra 3
5	Title 4	Artist 4	2013	#4	Len 4	Comment 4	a	Extra 4
6	Title 5	Artist 4	2000	#5	Len 5	Comment 5	a	Extra 5
7	Title 6	Artist 4	2006	#6	Len 6	Comment 6	c	Extra 6
DB

In 'Genre a' worksheet, copy headings from 'DB' then enter this formula in cell A2 only. The other results wil automatically 'spill' to all required rows/columns

Dan Wilson 2020-07-19 1.xlsm
	A	B	C	D	E	F	G	H
1	Title	Artist	Year	#	Length	Comments	Genre	Extra
2	Title 1	Artist 4	2008	#1	Len 1	Comment 1	a	Extra 1
3	Title 4	Artist 4	2013	#4	Len 4	Comment 4	a	Extra 4
4	Title 5	Artist 4	2000	#5	Len 5	Comment 5	a	Extra 5
5
Genre a

Cell Formulas
Range	Formula
A2:H4	A2	=FILTER(Table1,Table1[Genre]="a","")
Dynamic array formulas.

The genre sheets will automatically update if rows are added to or removed from the 'DB' table.

 

[Logit]

.
Download example workbook : Music Genres.xlsm

Updated macro :

Option Explicit

Sub Create_separate_worksheet()
    Dim c As Range, sh As Worksheet, Ky As Variant
    
    Set sh = Sheets(1)
    Application.ScreenUpdating = False
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("G2", sh.Range("G" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next c
        For Each Ky In .Keys
            sh.Range("G1").AutoFilter 1, Ky
            Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
            sh.AutoFilter.Range.EntireRow.Copy Range("G1")
        Next Ky
    End With
    Application.ScreenUpdating = True
    sh.ShowAllData
End Sub

You do not have to copy your music list into the download workbook. Simply paste the macro into your existing workbook, into
a Routine Module, create a commandbutton on the first shirt as shown in the example workbook and connect the button to the
macro. The macro will do the rest.

 

[Dan Wilson]

Good day Peter_SSs and Logit. I appreciate the responses that you both sent. I thought I was doing well with Excel, but you have both shown me that I am still learning. This problem began when I decided to create a series of music shows to be broadcast over the internet. My music folder contains almost 5,000 entries, all in MP3 format. As I started to put the show together, I realized that I needed a workbook that contained not only the Title of each song, but other parameters about each song buried in the Shell Properties of the file. I was given a Macro that extracted all of the properties that I wanted from the Music folder and left me with a database that I could sort as I needed. Then I discovered that the sorting process was taking a while depending on the property chosen. At that point I decided to make mini copies of the master database (DB) putting all the songs of a particular Genre, each in a separate worksheet. After doing all of that manually, I thought... why not use a Macro to make the mini database worksheets. I'm sure that both of your suggestions will work, but if I understand them correctly, I will not be left with a worksheet that can be sorted. Then came the problem of songs being added to my Music folder. The entire process would have to be done over to include the added songs. Therefore, this whole project needs to be Macro controlled with the end result being fixed databases that can be sorted. I am sorry to be such a nitpicker, but that is my problem. If you have a solution, I will appreciate it greatly. If you tell me to do it manually I will accept that also with my thanks for all that you have done so far.
Thank you, Dan Wilson...

 

[Logit]

The macro I recommended will not delete the Master Sheet. That will remain intact.

Why not post your existing workbook to a cloud site for download. Then I can implement the code for you and make certain it functions as desired.

Post the download link here for review.

 

[Dan Wilson]

Good day Logit. Thank you for sticking with this one. I don't use the cloud. I do have a Dropbox app. The XL2BB function gives me the impression that I can copy the entire workbook onto a reply to you. I had a difficult time downloading and installing the XL2BB app on my computer. Now, I can't figure out how to make it work. It's accepted as an excel Add-In, but the instructions on how to use it say I should have a MrExcel Forum command button, but I can't find it. I'm getting very frustrated at this point. Thank you, Dan Wilson...

 

[Peter_SSs]

The XL2BB function gives me the impression that I can copy the entire workbook onto a reply

It is intended for small sections of one or more worksheets.
From here:

XL2BB captures the cells in the selected range and converts it to a special BB code hash that you can paste into a post.

. It's accepted as an excel Add-In, but the instructions on how to use it say I should have a MrExcel Forum command button, but I can't find it.

If you have installed and activated correctly as per these instructions then you should get a new tab in your Excel ribbon

.. and on that ribbon tab:

 

[Logit]

Place the workbook on DropBox.com and provide the link for download.