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

 

[Jeffrey Mahoney]

My first blush on that is that you are maxing out the capacity of memory and resources to create 500 sheets. Are they large sheets? Lots of formulas and values?

 

[mnyankee1]

Largest sheets are less than 30 rows of data. All are 21 columns wide. No formulas at all. Thanks!

 

[Jeffrey Mahoney]

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?

 

[Logit]

This code creates new tabs ... Tab names are listed on the MASTER LIST sheet / Col A.

It then copies what is on Sheet 1 (Range A1: N56) and pastes to each of the newly created sheets.

I just created 500 new sheets and copied the contents from Sheet 1 (Range A1: N56) to all new sheets in under 5 seconds ... no errors.

Option Explicit


Sub CreateSheetsFromAList()
        Dim MyCell As Range, MyRange As Range
        Application.ScreenUpdating = False
         
        Set MyRange = Sheets("Master List").Range("A2")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))
        
        
        For Each MyCell In MyRange
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
            
        Next MyCell
        Application.ScreenUpdating = True
        Sheets("Master List").Activate
        Sheets("Master List").Range("D1").Select
End Sub


Sub CopyPaste()
Dim WS_Count As Integer
Dim I As Integer
Dim Source As Range
    Application.ScreenUpdating = False
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    Set Source = ThisWorkbook.Worksheets("Sheet1").Range("A1:Z56")


    ' Begin the loop.
    For I = 1 To WS_Count
        With ThisWorkbook.Worksheets(I)
            If .Name <> "Master List" And .Name <> "Sheet1" Then
                Source.Copy
                .Paste
            End If
        End With
    Next I
    Application.ScreenUpdating = True
    Sheets("Master List").Activate
    Sheets("Master List").Range("D1").Select
End Sub

 

[mnyankee1]

Thanks, I'll see if I can adopt any of this code but it's pretty simplistic vs what I am currently doing. I am not just copying the same data to every sheet. My code filters the data, creates a new sheet for that data, copies the data to new sheet and then moves to the next filter.

 

[Logit]

I'm having a difficult time following your code. Am I correct that it filters the same data repetitively ? Then copies and pastes that data to the new sheet ?

Or post filtering ... is the data different each time ? (I assume this is the case, otherwise why would it be necessary to filter it each time)

If the data is repetitively the same ... it would seem creating a template of the filtered data once, to be copied / paste is the faster method.

 

[mnyankee1]

The code copies column A (customer number) to column W then removes duplicates. It then filters the main dataset based on the customer number in column W, creates a new sheet and copies all data matching that customer number to the new sheet. It then loops to the next customer number and so on. Problem is it just hangs up. I think it is a resource issue, even with no other apps running.

 

[Logit]

So ... is the data after filtering, different each time or is it the same ?

 

[mnyankee1]

Sorry, the data is different each time after filtering.