How to sort rows in Excel into different sheets using a filter

**lukeafuller** · 06-22-2014, 10:46 PM

Hello!

I am new to this forum and I suspect I shall be asking a lot of questions. I have a basic understanding of Excel but have learnt a lot over the last few weeks trying to set up a Master Price List that is synced with SharePoint so that people will be able to view it online and sync it to their costing sheets.

I have had trouble syncing the MPL to SharePoint Online because of Sharepoint's "resource throttling." To go around this I need to get the lists down to 5000 rows or less for SharePoint to sync data correctly. I have tried setting up a macro using the advanced filter to sort my 15000 entries alphabetically and move them into separate sheets but I have found this is way too heavy and doesn't update the sub-sheets when the main price is altered. There must be a way of using a filter that will automatically move data into the various sheets without the use of a heavy macro, but I understand if one is necessary to do so.

I'm sorry if I posted this n the wrong place on the forum!

Cheers and thanks in advance!

Luke

**rcm** · 06-22-2014, 11:27 PM

please send a sample

**lukeafuller** · 06-23-2014, 01:45 AM

Hope that helps its only a few rows.

Thanks for the reply!

Luke

**rcm** · 06-23-2014, 02:50 AM

how are they linked to the sub-sheets?

**lukeafuller** · 06-23-2014, 05:23 AM

At the moment there is no link because I just uploaded a portion or the master price list. That is what I am trying to figure out. I tried using a macro and it was way too heavy ad it was keep bugging out with errors. I watched a few videos to try and help me but they didn't seem to help much. When I run the macro it just deletes my master price list.

**lukeafuller** · 06-24-2014, 04:29 AM

I have been trying a different code which looks something like this:

Private Sub CommandButton1_Click()
Sheet1.Activate
Sheet1.Range("A10:Z10").Select
Selection.Copy
Sheet2.Select
Range("A1").PasteSpecial
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Sheet2.Range("C4").Select
Sheet1.Select
Application.CutCopyMode = False
Sheet1.Range("C15").Select
Sheet2.Select
Sheet1.Range("A10:Z18916").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheet1.Range("AB10:AB11"), CopyToRange:=Sheet2.Range("A1:Z1"), Unique:=True
End Sub

It works but it takes 1 minute to filter. Is there anyway of streamlining this?

How to sort rows in Excel into different sheets using a filter

LinkBack

Thread Tools

Rate This Thread

Display

How to sort rows in Excel into different sheets using a filter

Re: How to sort rows in Excel into different sheets using a filter

Re: How to sort rows in Excel into different sheets using a filter

Re: How to sort rows in Excel into different sheets using a filter

Re: How to sort rows in Excel into different sheets using a filter

Re: How to sort rows in Excel into different sheets using a filter

Thread Information

Users Browsing this Thread

Similar Threads

Filter and sort rows with several dates

Error 438 when running sort, filter, and copy across sheets (2003, written in 2010)

Sort and combine records/rows from two sheets.

Sort / Filter rows

[SOLVED] Columns A - BY and 336 rows.Too much to filter&sort?Lose info.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions