copying worksheets in a workbook limit?

G

Gary

I have routine that updates previous workbooks by copying sheets within the
new workbook and getting the old sheet information.

Everything works great, but for some reason after more than 45 sheets are
copied it errors out.

Some of my workbooks contain over 100 sheets.

Is this a memory thing or limit thing?
 
S

sqlfan13

Microsoft says to save and copy the workbook every couple of copies -
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw -
but I have been able to get around this by creating a new workbook on the fly
and copying the sheet into the new workbook and copying it back in from there.

Here is my code:

<Code>
Public Sub SaveChartTemplate()

Dim sCurrentWorkbook As String
Dim wb As Workbook
Dim sNewWorkbook As String
Dim i As Long
Dim sCurPath As String

sCurrentWorkbook = ThisWorkbook.Name

'create a new workbook
Set wb = Workbooks.Add
sNewWorkbook = wb.Name

'copy chart template into new workbook
Workbooks(sCurrentWorkbook).Charts(ChartTemplateSheetName).Copy _
Before:=wb.Sheets(1)

'delete all other sheets from new workbook
Application.DisplayAlerts = False
For i = wb.Sheets.Count To 2 Step -1
wb.Sheets(i).Delete
Next i

'save chart template as ChartTemplate.xlt
sCurPath = Workbooks(sCurrentWorkbook).Path
wb.SaveAs Filename:=sCurPath & "\" & ChartTemplateWbName
wb.Close
Application.DisplayAlerts = True

Set wb = Nothing

End Sub
<End Code>

Hope this helps
 
Top