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

 

[Fluff]

As your data is not in a table, try this version of Peter's code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim wsDB As Worksheet
  Dim sGenre As String
  
  If LCase(Cells(1, Target.Column)) = "genre" Then
    If Len(Target.Value) > 0 Then
      sGenre = Target.Value
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      On Error Resume Next
      Sheets(sGenre).Delete
      On Error GoTo 0
      Application.DisplayAlerts = True
      Me.Copy After:=Me
      With Sheets(Me.Index + 1)
        .Name = sGenre
        .Range("A1").AutoFilter Field:=Target.Column, Criteria1:="<>" & sGenre  '<- Check column heading
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilterMode = False
      End With
      Application.ScreenUpdating = True
    End If
  End If
End Sub

 

[dmt32]

OK, I give up. Either no-one is reading what I write, or I am not writing what I want in a way that can be understood.

I thought I had read your request correctly

I would like to create additional worksheets in the same workbook dedicated to each Genre of music

which is what my suggestion & presumably, the others do as well?

But from your response, it clearly is not what you wanted.

Sorry suggestion is of no help to you


Dave

 

[Logit]

.
Music Database.xlsm

Option Explicit

Sub SingleGenre()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim wrd As String
Dim i As Integer

Set ws1 = Sheets("DB")
Set ws2 = Sheet1

ws2.Cells.ClearContents

lRow = Cells(Rows.Count, 7).End(xlUp).Row

wrd = InputBox("Enter GENRE", "Genre Filter")

Application.ScreenUpdating = False

For i = 1 To lRow
    If ws1.Cells(i, 7) = wrd Then
        ws1.Cells(i, 7).EntireRow.Copy
        ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next i

Application.ScreenUpdating = True

ws2.Name = wrd

MsgBox "Done !"

End Sub

 

[Dan Wilson]

WE HAVE A WINNER!!! Thank you Logit! That is exactly what I wanted. I still don't understand everything that the code does but I can work on that. Logit, I thought you gave up on me, but you came through. Thank you again for your persistence. I should have known. DMT, your comments are completely right. Apparently the problem is in my writing, not anyone else's understanding. I will work on that. Thank you for your patience. Fluff, thank you for your entry. I don't know what is going on,, but after entering your Macro into the workbook, the Macro function would not recognize that the macro was there. I tried changing the Private Sub to Sub, but that did not work either. The macro is there when I examine the Module, but it does not show up when I list the Macros. Peter, I read the Guide as you suggested and will comply in the future. I'm impressed with the patience of everyone involved. I will try to emulate that. Thanks again, everyone. I think I will be busy learning for a while.
Dan Wilson...

 

[Fluff]

The code needs to go in the database sheet module, then you just double click the genre you want & hey-presto it's done.

 

[Logit]

You are welcome.

 

[Peter_SSs]

It sounds like you have not entered Fluff's code or probably my earlier code in the right place. Detailed instructions were given in post 18 to ensure that it was in the right place but clearly something has gone wrong. I am not suggesting that you need to stop using Logit's code (although it does error or produce unwanted results under some circumstances for me) but to prove the concept of my earlier code I have modified it somewhat below & included it in this sample which you should be able to download & test:
Dan Wilson Music Database 2.xlsm

All you need to do is double-click any cell in the Genre column on the DB sheet containing the Genre that you are interested in.
I think you will also see a very significant difference in the the time it takes to generate your new single-genre sheet.
Other possible advantage of this approach are

• It eliminates the possibility of spelling errors when choosing your genre.
• If you decide to add or remove columns to the left of the Genre column the code will not need changing

The code is reproduced below for those that do not wish to download the linked file.
It assumes the DB sheet data is contained in a formal table (this could be changed if definitely against using a formal table) and that sheet "DB" is the left-most sheet in the workbook.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim wsDB As Worksheet
  Dim sGenre As String
  
  If Not Intersect(Target, ActiveSheet.ListObjects(1).ListColumns("Genre").Range) Is Nothing Then '<- Check column heading
    If Len(Target.Value) > 0 Then
      Cancel = True
      Set wsDB = ActiveSheet
      sGenre = Target.Value
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      On Error Resume Next
      Sheets(2).Delete
      On Error GoTo 0
      Application.DisplayAlerts = True
      wsDB.Copy After:=ActiveSheet
      With Sheets(2).ListObjects(1)
        .Parent.Name = sGenre
        .AutoFilter.ShowAllData
        .Range.AutoFilter Field:=.ListColumns("Genre").Index, Criteria1:="<>" & sGenre  '<- Check column heading
        .Range.Offset(1).EntireRow.Delete
        .AutoFilter.ShowAllData
      End With
      Application.ScreenUpdating = True
    End If
  End If
End Sub

 

[Dan Wilson]

Good day all. This is going to be a long reply. I have reached the point where I realize that I have been the problem all along in this venture. I apologize for that. I can see where I provided requests and answers to your responses that did not express the complete situation. I have been working with this Forum since 2006 having posted 391 entries. To date, they have all been completed with a few needing extra replies. I have learned quite bit in that time, but I am nowhere near being as proficient as those of you who have been part of this venture. I have created some formulas that I am proud of due to their complexity, but I am having trouble understanding some of the functions listed in the responses to this issue. I have not had any formal Excel training, which is probably part of the problem. Everything that I have done in the last 20 years has been learned by experimentation and by help from this Forum. I would be lost without the Forum.

With all of that said, let me detail once more, from the beginning, what I have and what I would like to be able to do. Some of this may sound extreme, but I want to make all the conditions known. Over the last 15 years I have been creating a Music folder on my computer. That folder is titled “MP3 normalized” and resides in a folder titled “Music” on the “C” drive. The folder contains songs of 32 different Genres from 1940 to 2001. The music was used as a library for a radio show that I hosted for 10 years from 2004 to 2014. After retiring from the corporate world in 2014, I ended the radio show and moved. My goal now is to create and publish a weekly music show on the internet using the music in my library. There is also the probability that I will be adding more songs to the music library as this venture continues. The music folder at this point contains almost 5,000 songs, all edited and stored in MP3 format, ready to use. Part of the editing of each song was to insert items in the Shell Properties of each file to identify particulars such as the Intro time, Chart Number, Title (without the .mp3 file extension), Artist, Year of release, Length. Genre, and Extras (miscellaneous data).

In order to make the music list more usable, it became necessary to obtain a Macro that would access each song in the music folder and place the desired Shell Properties into the worksheet. That was accomplished under another request answered by Eric W. The reply was a Macro that ran on a worksheet titled “Home”. When executed, the Macro created a second worksheet titled “DB” that contained all the songs from the music folder showing the 8 columns of data from the Shell Properties. This worksheet is apparently not in Table Format. At that point, I then asked if someone could provide a Macro to search for a given Genre (one of the worksheet Columns). This is where the process became complicated because I did not make my desires clear. Rather than go through all the mess that I created, let me define as clearly as I can what I want to do.

The Macro that creates the DB worksheet does exactly what I want. Here is what I want to able to do after the DB worksheet is created:

1. Search the DB worksheet for a particular Genre and return all the songs that match that Genre in a new worksheet titled with the Genre and contain the 8 Columns of data for each song.

2. The new worksheet with the desired Genre matches should remain in place and not be replaced by another Genre search.

3. Search the DB for another Genre and return all the songs that match that Genre in another new worksheet titled with the Genre as done in item 1.

4. This process needs to able to be done for any number of Genres, but not all 32 Genres.

5. The Macro that does the Genre search must allow me to add functions to copy Row 1 of the DB (Column titles), set the Column Width, Column Format, Bordering, Top Row Freeze, Center the Column titles in Row 1, Set Columns A, B, and H to Wrap Text, Page Layout to Landscape and any other display functions that may be needed.

6. Once the Genre search Macro is completed, I can then add the functions described above, store each Macro in the Developer Modules with each Macro being titled by the Genre to be found.

7. I can then create a separate worksheet containing Command Buttons that will execute a Genre search by choice.

The reasons for the conditions above will allow me to renew the entire process as new songs are added to the music folder. When the time comes to include newly added songs to the music folder, I can create a completely new workbook containing the Home worksheet with the Macro to create the DB worksheet and copy all the Genre search Macros into that workbook as well. I feel confident enough to edit the Macros as necessary. The weekly radio show may also go in different directions each week, requiring music selections from different Genres. At this point, I have no desire to try and learn Tables as I do not think that would work with the conditions expressed above.

You have all gone out of your way to provide me with answers that will work, but so far the answers are leading me in a direction that I know nothing about or do not conform the requirements posted above. There appear to be a multitude of functions and options included in the suggested Macros that I do not understand yet. I do hope that I am not asking for too much. If so, please let me know. I can do what I want manually with what I currently have, it will just take longer. Thank you for getting this far and I hope to hear from someone.
Dan Wilson...