Issues creating hundreds of worksheets

[mnyankee1]

Experts,

The following code uses filters to copy like sets of data to their own sheets within a workbook. This works fantastic if the data results in say less than 100 sheets being created. Anything beyond that and the macro never finishes (well I have let it run for up to 1 hour before giving up). My dataset has suddenly quadrupled and I have a need to create up to 500 sheets in a single workbook. 1) is that even possible? 2) how can my code be tweaked to handle this?

Thanks!

Sub DataCopyToOwnSheetUsingFilters()
Dim v As String
Dim i As Integer
Application.ScreenUpdating = False  'speeds up the process by not refreshing the screen
Sheets("Copy From").Activate
Range("A:A").Copy
Range("W1").PasteSpecial (xlPasteAll)
Range("W1").RemoveDuplicates 1, xlYes

For i = 2 To Range("W600").End(xlUp).Row   'change "2000" to a number that allows for a cushion
      
      v = Cells.Range("W" & i)
      Range("a1").AutoFilter 1, Range("W" & i)
      Range("a1").CurrentRegion.Copy
      
      On Error GoTo ErrMsg
      Sheets.Add.Name = (v)
      
      ActiveCell.PasteSpecial (xlPasteAll)
      Sheets(v).Columns("A:U").EntireColumn.AutoFit
            
      Sheets("Copy From").Activate
      
      Range("a1").AutoFilter
            
Next i
Range("W:W").Delete
Application.ScreenUpdating = True   'turns refreshing back on
Exit Sub
ErrMsg:
Application.DisplayAlerts = False
Sheets(ActiveSheet.Name).Delete
Application.DisplayAlerts = True
Sheets("Copy From").Activate
Range("a1").AutoFilter
Range("W:W").Delete
MsgBox ("That sheet, " & v & ", already exists.  Please start over with a clean workbook."), , "OOPS, USER ERROR!"
End Sub

 

[Logit]

Ok .. then I don't see an advantage your code vs my code. You are most likely correct in that your issue is the computer resources.

 

[Ruddles]

If I understand correctly, you're creating many different worksheets, each containing a filtered subset of the original worksheet.

Perhaps instead of creating all those worksheets - which just include copies of the data from your original worksheet, you could mark the records in the original worksheet in some way to indicate which worksheet they would have been in. Then instead of looking at those additional worksheets, you filter the records in original worksheet based on which worksheet they would have been in.

Did I explain that clearly?

 

[Ruddles]

Alternatively, if there's room, instead of distinct worksheets, place each new subset of the data at a predetermined row in the original sheet, say starting from row 1000, 1100, 1200, etc.

Or copy them to a new sheet but place a marker in column Z (for example) indicating which subset of the data it is.

 

[FDibbins]

Just out of curiosity, why do you need 500 sheets? Is there another way we can parse your data without having to do all that?

I would also strongly question the need for even 50 sheets, let along 500!! How on earth would you ever find anything in them?
If you are making certain summaries from a master sheet, there are far easier/more efficient ways to do that, using just a few summary sheets

 

[mnyankee1]

Solved! I was able to get this running by adding Application.CutCopyMode=False and a counter that saves the workbook after every 50 sheets that have been created (ActiveWorkbook.Save). The 50 can obviously be adjusted but I seem to get the best performance with it at 50. The whole thing takes 8-10 minutes to run and creates a little over 500 worksheets. Granted this is something that will only be used monthly but it's saving people a lot of hours vs their previous process.

Thanks again for your suggestions!