Memory problem from macro creating and naming sheets from Array?

**acj06** · 10-26-2011, 06:26 PM

In the attached Workbook I have a macro that copies a master sheet and then renames it according to a range in the 'Ctrl' sheet. Unfortunately before all the sheets can be created the memory fills up and the macro breaks.

If someone could tell me how to clear the memory after each sheet has been created it would be much appreciated! Also if possible it would be great to add some lines that check if the number of names in the list is longer than the existing no. of sheets, if so continue, if not exit.

Any help much appreciated.

Best

Alex

**JBeaucaire** · 10-27-2011, 12:37 AM

What is the error message you get?
What line of code is highlighted when you DEBUG at the macro error?
Where is the sample workbook? (Zip it up if necessary, or trim it down.)

**acj06** · 10-27-2011, 05:05 AM

Sorry, the attachment has failed twice now...here is the code:


Sub Generat_Comps()
    Dim x As Integer
    Dim NameArray(1)
    Dim ClassBArray(2)
    Dim PreferredArray(3)
    Dim SavingsArray(4)
    
    NameArray(1) = Range("B11:B50").Value
    ClassBArray(2) = Range("C11:C50").Value
    PreferredArray(3) = Range("D11:D50").Value
    SavingsArray(4) = Range("E11:E50").Value
    
    x = Range("B52").Value

    For numtimes = 1 To x
        'Loop using x as the index number to make x number copies.
        'Replace "Master (2)" with the name of the sheet to be copied.
        
            ActiveWorkbook.Sheets("Master").Copy _
            Before:=ActiveWorkbook.Sheets("FX")
               
   
    Sheets("Master (2)").Range("D4") = NameArray(1)(numtimes, 1)
    Sheets("Master (2)").Range("E4") = ClassBArray(2)(numtimes, 1)
    Sheets("Master (2)").Range("F4") = PreferredArray(3)(numtimes, 1)
    Sheets("Master (2)").Range("G4") = SavingsArray(4)(numtimes, 1)
    
    'then
    
    Sheets("Master (2)").Name = NameArray(1)(numtimes, 1)
          
    Next
    
End Sub

The error message is:
Run-Time error 1004

Then points to:

 ActiveWorkbook.Sheets("Master").Copy _
            Before:=ActiveWorkbook.Sheets("FX")

Thanks for the help

**JBeaucaire** · 10-27-2011, 01:22 PM

1) The uploaded wb has no sheet named Master and no sheet named FX. Both of those truths result in the error you've seen.

2) The uploaded wb has no reference data to create arrays from

3) The beginning of the macro does not include instructions to the array commands on which worksheet to create those arrays from

4) The uploaded wb does not include the macro you've referenced.

**acj06** · 10-27-2011, 02:23 PM

Sorry:
I had to strip out the private information i.e loosing the ranges etc, I don't know why the workbook doesn't contain the macro as it does for me. The sheet names was an error...

I have got the macro working by using a template instead of copying a sheet. Using the code below:

Sub Generate_Comps()

   'Check to see if you have already built the comp sheet
   If SheetCount > Range("B52").Value Then
   MsgBox "You have already built the comps sheet, delete the created comps and then re-build", vbOKOnly, "title"
   End
   Else
 
   'If you haven't built the comp sheet then build!
   
    Dim x As Integer
    Dim NameArray(1)
    Dim ClassBArray(2)
    Dim PreferredArray(3)
    Dim SavingsArray(4)
    Dim ValDate(5)
    Dim StartDate(6)
    
    NameArray(1) = Range("B11:B50").Value
    ClassBArray(2) = Range("C11:C50").Value
    PreferredArray(3) = Range("D11:D50").Value
    SavingsArray(4) = Range("E11:E50").Value
    ValDate(5) = Sheets("Database").Range("I3:I3")
    StartDate(6) = Sheets("Database").Range("I4:I4")
    
    x = Range("B52").Value

    For numtimes = 1 To x
        'Loop using x as the index number to make x number copies.
        'Replace "Master" with the name of the sheet to be copied.
        
        Sheets.Add Type:="Location of Template", Before:=ActiveWorkbook.Sheets("Log")
               
   
    Sheets("Master").Range("D4") = NameArray(1)(numtimes, 1)
    Sheets("Master").Range("E4") = ClassBArray(2)(numtimes, 1)
    Sheets("Master").Range("F4") = PreferredArray(3)(numtimes, 1)
    Sheets("Master").Range("G4") = SavingsArray(4)(numtimes, 1)
    Sheets("Master").Range("D5") = ValDate(5)
    Sheets("Master").Range("D8") = StartDate(6)
    
    'then
    
    Sheets("Master").Name = NameArray(1)(numtimes, 1)
          
    Next
  End If
End Sub

Function SheetCount(Optional Worksheets_only As Boolean) As Long
     
    If Worksheets_only = True Then
        SheetCount = Worksheets.Count
    Else
        SheetCount = Sheets.Count
    End If
     
End Function

This is fine, but Ideally i'd like to keep it in the workbook as this is a model that will be used by people that a) Don't have any knowledge of VBA and won't know how to point to the Template location...

I've attached a workbook that I just tested and works, it won't let me attach the template so insert a excel template file with one sheet named 'Master'...You will need to point to it's location in the code in 'Location of template'
Sorry for failing to do this before.

I think I want to copy a sheet in the sheet and then clear the clipboard after every iteration, whats your thoughts on this?
Regards
Alex