MrExcel Publishing
Your One Stop for Excel Tips & Solutions

What is the most efficient way of breaking a large data set up into categories on worksheets


Posted by Adam K on April 09, 2001 3:30 PM

I have a list of stocks.
In the list, I have a SECTOR field

I want to do a loop and create a worksheet for each SECTOR and paste the data that is in that sector into that worksheet.

I have tried doing a loop where it:
1. autofilters the frontsheet
2. copies visible cells
3. pastes on the appropriate sheet.

PROBLEM: this takes so long. It hangs on the paste for minutes. Also the autofilter takes a while

I have also tried
1. Sorting by SECTOR
2. grabbing a dynamic range of data that fits that sector
3. Copying and pasting this

PROBLEM: Again the pasting takes a very long time. The average amount of data is about 25 rows by 50 columns.

This is killing me.

There has to be an efficient quick way of doing this.

NOTE: I need to copy formulas and formatting.

Thanks

Adam


Posted by Dave Hawley on April 09, 2001 11:32 PM

Hi Adam

This code will do it pretty quick. You need to run it from your sheet will All sectors on. It will put a list of unique sectors in Column E, filter your table down by sectors and copy visible cells in Column A:D and paste them on a sheet created and named after each sector. You simply need to change the ranges to suit.

Sub CopySectors()
'Copy text only from Column A
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim i As Integer
Dim ThisSheet As Worksheet
Dim Sector As String
Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet

With ThisSheet
'Turn off AutoFilters
.AutoFilterMode = False
'Filter out all unique sectors _
and put them in Column E
.Columns(2).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:= _
.Range("E1"), Unique:=True

'Loop through and filter by all unique sectors _
then create a worsheet named after each sector.
'Then copy the data for that sector to it's sheet
For i = 2 To WorksheetFunction.CountA(.Columns("E:E"))
Sector = .Cells(i, 5)

'Turn on Autofilter if needed
If .AutoFilterMode = False Then .Rows(1).AutoFilter

'Filter down by sector and copy visible cells _
of columns A:D
.Rows(1).AutoFilter Field:=2, Criteria1:=Sector
.Columns("A:D").SpecialCells(xlVisible).Copy

'Add a sheet named after sector
Sheets.Add().Name = Sector

'Paste new data on sheet
ActiveSheet.Paste

Next i

.AutoFilterMode = False
End With

Set ThisSheet = Nothing
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Dave

OzGrid Business Applications

Posted by adam on April 10, 2001 9:01 AM

Dave,

The code is very efficient. The problem is that I need to paste all because I want to formulas to copy over. The paste line takes a few minutes each time to just go over that one line. I have about 75 columns and They are mostly index formulas.

Adam

'Turn on Autofilter if needed If .AutoFilterMode = False Then .Rows(1).AutoFilter 'Filter down by sector and copy visible cells _ of columns A:D .Rows(1).AutoFilter Field:=2, Criteria1:=Sector .Columns("A:D").SpecialCells(xlVisible).Copy 'Add a sheet named after sector Sheets.Add().Name = Sector 'Paste new data on sheet ActiveSheet.Paste .AutoFilterMode = False

Posted by adam k on April 10, 2001 10:53 AM

Dave I am getting a memory error after about the 7th or 8th sheet. is there any way of getting around this ??

Thanks,

Adam

, 'Turn on Autofilter if needed If .AutoFilterMode = False Then .Rows(1).AutoFilter 'Filter down by sector and copy visible cells _ of columns A:D .Rows(1).AutoFilter Field:=2, Criteria1:=Sector .Columns("A:D").SpecialCells(xlVisible).Copy 'Add a sheet named after sector Sheets.Add().Name = Sector 'Paste new data on sheet ActiveSheet.Paste .AutoFilterMode = False


Posted by Dave Hawley on April 10, 2001 7:45 PM

Hi Adam

I a bit confused with your first comment about having to copy over formulas as well. The code WILL copy over formulas, formatting and content.

Regarding the "out of memory problem" I just tested the code again on a sheet with 30 different sectors and it ran without any memory problems and took about 10 seconds to complete.

Read this information below regarding memory problems:

Don't save the Workbooks in multiple formats unless really needed. Saving Workbooks as more than one format ( 95,97 and 97 ) can blow out file size by > 75%

Also see this article on OUTLOOK http://support.microsoft.com/support/kb/articles/Q167/0/81.ASP

Only have the "Add-ins" you need installed

If your using VBA then NOT releasing Objects from memory is another cause, always set them to nothing on completion:

Set Wsht as Worksheet(1)

Your code

Set Wsht =Nothing


Another big cause of problems is out of date Printer drivers make sure you have the latest. You can do this by going to the Website of your printer manufacture.
See this article: http://support.microsoft.com/support/kb/articles/Q165/9/85.ASP

Keeping the sheet zoom at 100% can also be helpful


And last but by no means least, delete all Temp files, Do a scan disk, empty the re-cycle bin then do a disk de-frag and re-boot.

Dave

OzGrid Business Applications