welcome To The One Thimble Blog
The Free PDF Pattern Directory which Nerissa from Spreadsheet Geek made for us has been a big hit! Thankyou! I’ve really loved seeing you share it with your friends and let us know what you thought about it! If you missed it you can read the post and download your free copy HERE.
We’ve had some queries as to how to add extra sortable columns to the spreadsheet (eg if you’d like to be able to sort your patterns by fabric type etc.) so Nerissa has done us up another blog post explaining how to do this.
I had a little play this morning using her instructions and added the fabric type column people had been requesting and pre-loaded this version with all the One Thimble patterns from Issue 1-7.
You can download it here -> Pattern-Directory-PC-PrefilledOT
If you’d like to know how to add extra sortable columns to the spreadsheet read on!
One Thimble PDF Pattern Directory – How to add in another sort button/macro
To add a new button:
- Right click on one of the other orange sort buttons. Select Copy
- Right click where you want the new button. Select Paste (Use Destination Theme)
- Edit button text to suit
- Right click new button, and select Assign Macro. Select a macro and click Edit. It doesn’t matter which one you choose at this stage, this is just an easy way to get into the Visual Basic.
- Scroll down past all the code showing, to the bottom of the screen. After the last ‘End Sub’, copy and paste the code below, then make your changes to the cell ranges etc. (Instructions on how to customise the code is at the very end)
- Save your work – click the disc icon near the top left of the screen. Close this Visual Basic window.
- Again, right click on the new button, select Assign Macro, and your new macro should be listed. Select your new macro, and hit OK
- Test it out!
Below is the code you need to cut and paste in:
‘sort patterns by name, then designer
Dim ws As Worksheet
Set ws = Worksheets(“Pattern Directory”)
ws.Sort.SortFields.Add Key:=Range(“B4:B2000“), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add Key:=Range(“C4:C2000“), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
Here is the “How to” for customising the code
|Code to Change||Description of function|
|Sub SortPatterns_Click()||This is the macro name. Change the red text to something the quickly describes your new sort function. The ‘_Click()’ prompts the macro to run upon clicking the button the macro is linked to|
|‘sort patterns by name, then designer||This is not actually code, but a comment differentiated by the ‘ (apostrophe) before the text. When coding, it helps commentate the code at a first glance.|
|B4:B2000||This is the range that your cell range that you want to sort. If you want to add data to column G and sort, enter “G3:Gxxxx , with ‘xxxx’ representing the final row number you want to sort down to|
|B4:B2000||In the OT pattern directory, columns are first sorted by column ‘x’ then column ‘y’. In this code, the macro sorts column B first, then column C.|
|C4:C2000||This is the second column coded in to be sorted|
|B3:G2000||This cell range is the complete data which is being sorted – so even though you may be sorting column B, you want all the corresponding data on each row to stay together. If you add in extra columns, this may read B3:H5000. If you add extra columns you’ll need to change this code for all the sections so that the rows will stay together if you sort by any of the other macrosNB that the row starts at row 3 here. This ‘Header = xlYes’ part is telling the macro not to sort the first row because this data is a header/title|
|B4||This little code is putting your cursor back in cell B4, or the top of the column you just sorted. I do this so it doesn’t end up somewhere in cell AZ65851|