Subscribe to Our Newsletter

welcome To The One Thimble Blog

How to Further customise your Pattern Directory

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!

How to Add extra columns to your free PDF Directory Blog post flyer

One Thimble PDF Pattern Directory – How to add in another sort button/macro

To add a new button:

  1. Right click on one of the other orange sort buttons. Select Copy 
  2. Right click where you want the new button. Select Paste (Use Destination Theme)
  3. Edit button text to suit
  4. 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.
  5. 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)
  6. Save your work – click the disc icon near the top left of the screen. Close this Visual Basic window.
  7. Again, right click on the new button, select Assign Macro, and your new macro should be listed. Select your new macro, and hit OK
  8. Test it out!

Below is the code you need to cut and paste in:

 

Sub SortPatterns_Click()

‘sort patterns by name, then designer

Dim ws As Worksheet

Set ws = Worksheets(“Pattern Directory”)

Range(“B4:B2000“).Select

ws.Sort.SortFields.Clear

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

With ws.Sort

.SetRange Range(“B3:G2000“)

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range(“B4“).Select

End Sub

 

 

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

 

{ 1 comment }

 


1 Comment to “How to Further customise your Pattern Directory”

  1. Cucicucicoo: Ecological Living 09/06/2015 at 9:05 am

    Wow, thanks Nerissa and Jen! Although I have to say, that code looks a little scary, so I’m not sure if I’ll ever actually use it…. 🙂 Lisa

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *