- Have turned off font scaling,
- Do not use the clipboard for copying
- Text works fine, pictures work fine, but not charts
I also read a MS Knowledge Base Article 264986 for XL97 which said there was
an error in copying worksheets containing charts.
In in my actual application, I add a set amount of graphs, copy the
worksheet to another workbook, and then I destroy the worksheet. Same
problem
Interestingly, I tried copying the graphs to the same sheet as the graph,
and there was no crash although there was a climb in private bytes, which
might be confounded by file size?? I'm not well clued up in terms of the
inter-relationships of memory.
If you want to try out what I do:
1. Open new excel sheet
2. Create a user form
3. Add a button on the form (Command1)
Paste following code:
'***************************************************************************
****
Option Explicit
'holds the table descriptors as a custom data type
Private Type TableCoords
nTopRow As Integer
nNoOfRows As Integer
nFirstColumn As Integer
nNoOfColumns As Integer
End Type
Dim m_nCurrentWorkSheetLinePosition As Integer
'##################################################################
'Method : Copy_Lines
'Purpose : Copies rows of text from the template to the report
sheet
'Parameters : ByVal nTopRow As Integer - the top row to be copied,
_
ByVal nBottomRow As Integer - the bottom row, _
ByVal sFromWorkSheet As String - the sheet from
where the data comes, _
Optional ByVal sToWorkSheet As String =
g_sWorkSheet_ReportPage - the data where the data is going to be placed
'Return :
'##################################################################
Private Sub Copy_Lines(ByVal nTopRow As Integer, _
ByVal nBottomRow As Integer, _
ByVal sFromWorkSheet As String, _
Optional ByVal sToWorkSheet As String)
On Error GoTo errHandler 'If any error occurs, go to the line
entitled ErrHandler 'catches any errors that may occur
Dim nOldPosition As Integer 'holds the previous row where last copied
nOldPosition = m_nCurrentWorkSheetLinePosition 'get the last line
position before it gets reset
Dim oFromSheet As Worksheet 'holds the sheet from where the data is
being taken
Set oFromSheet = Sheets(sFromWorkSheet) 'assign the variable to the from
sheet
Dim RightSideOfTemplateDimensions As TableCoords 'holds the coordinates
of the right side of the template
RightSideOfTemplateDimensions.nFirstColumn = oFromSheet.Cells(41,
6).Value 'get the first column
RightSideOfTemplateDimensions.nNoOfColumns = oFromSheet.Cells(41,
7).Value 'get the number of columns
Dim nLastcolumn As Integer 'holds the last column where data needs to be
pasted to
nLastcolumn = RightSideOfTemplateDimensions.nFirstColumn +
RightSideOfTemplateDimensions.nNoOfColumns 'get the last column
Dim oToSheet As Worksheet 'holds the sheet where data will be pasted
Set oToSheet = Sheets(sToWorkSheet) 'assign the variable to the to sheet
oFromSheet.Activate 'activate the from sheet
oFromSheet.Range(Cells(nTopRow, 1), Cells(nBottomRow,
10)).EntireRow.Copy
Destination:=oToSheet.Range(Cells(m_nCurrentWorkSheetLinePosition,
1).Address) 'copy the data in the selected area
m_nCurrentWorkSheetLinePosition = m_nCurrentWorkSheetLinePosition +
nBottomRow - nTopRow + 1 'set the current worksheet line position
' 'control flow: if the position has not changed, increment the worksheet
position
' If m_nCurrentWorkSheetLinePosition = nOldPosition Then
' m_nCurrentWorkSheetLinePosition = m_nCurrentWorkSheetLinePosition +
1
' End If
Set oToSheet = Nothing 'set the variable to nothing
Set oFromSheet = Nothing 'set the variable to nothing
Exit Sub
errHandler:
Set oToSheet = Nothing
Set oFromSheet = Nothing
Err.Raise Err.Number, Err.Source, Err.Description 'raise the error
End Sub
Private Sub CommandButton1_Click()
Dim nLoop As Integer
For nLoop = 1 To 5000
Copy_Lines 1, 5, "Sheet1", "Sheet2"
Next
End Sub
Private Sub UserForm_Activate()
m_nCurrentWorkSheetLinePosition = 1
End Sub
'***************************************************************************
*******
