Help with creating mini worksheets from major workbook

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
407
Office Version
  1. 365
Platform
  1. Windows
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...
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,721
.
VBA Code:
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

Active Member
Joined
Feb 5, 2006
Messages
407
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,911
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1TitleArtistYear#LengthCommentsGenreExtra
2Title 1Artist 42008#1Len 1Comment 1aExtra 1
3Title 2Artist 21993#2Len 2Comment 2bExtra 2
4Title 3Artist 11989#3Len 3Comment 3cExtra 3
5Title 4Artist 42013#4Len 4Comment 4aExtra 4
6Title 5Artist 42000#5Len 5Comment 5aExtra 5
7Title 6Artist 42006#6Len 6Comment 6cExtra 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
ABCDEFGH
1TitleArtistYear#LengthCommentsGenreExtra
2Title 1Artist 42008#1Len 1Comment 1aExtra 1
3Title 4Artist 42013#4Len 4Comment 4aExtra 4
4Title 5Artist 42000#5Len 5Comment 5aExtra 5
5
Genre a
Cell Formulas
RangeFormula
A2:H4A2=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

Well-known Member
Joined
Aug 31, 2016
Messages
3,721

ADVERTISEMENT

.
Download example workbook : Music Genres.xlsm

Updated macro :

VBA Code:
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

Active Member
Joined
Feb 5, 2006
Messages
407
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,721

ADVERTISEMENT

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

Active Member
Joined
Feb 5, 2006
Messages
407
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,911
Office Version
  1. 365
Platform
  1. Windows
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

1595224267595.png


.. and on that ribbon tab:

1595224575857.png
 
Solution

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,721
Place the workbook on DropBox.com and provide the link for download.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,574
Members
410,852
Latest member
WernerS
Top