Excel 97 Copy and Paste Error

[FamWS]

Hi,

I created 21 chart sheets using MS Excel XP and a macro to copy one of the chart to a new worksheet. It works perfectly fine under Excel 2K and XP. But in Excel 97, when I copy the chart, say 5th chart to a new worksheet, it gives me GPF error at the Paste command.

I suspect it is a memory problem, because copying the first 3 charts has no problem.

Anyone has any clue? Thanks for your help in advance.

Regards,
Fam

 

[nwspec]

I would try to replace Chart 4 with chart 1 2 or 3 just to test in case you have a code or command or formula in chart 4 that Excel 97 does not like.

Just an idea.

 

[FamWS]

Thanks for your clue, I have tried moving chart 4 to chart 1, copy and paste has no problem. When I try to copy chart 3 which is now chart 4, GPF error occurs.

Regards,
Fam

 

[xlbo]

Please post the code

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FamWS]

Sorry, the code is a bit long. The error occurs at DisplayChart:
:
ActiveSheet.Paste



Sub AllTempChartDisplay()
Dim l_i As Integer
Dim l_j As Integer
Dim l_ischar As Boolean
Dim l_vactiveworkbookname As String
Dim l_vactrow As Long
Dim l_vscount As Integer
Dim l_ffound As Boolean
Dim l_sheetcount As Integer
Dim ExPersonal As Excel.Application
Dim l_cnt As Integer

Application.ScreenUpdating = False

gv_benchmarkflag = False
l_ischar = True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
l_vactiveworkbookname = Application.ActiveWorkbook.Name

'Checking whether workbook activated or not
If Not l_vactiveworkbookname = "" Then
Application.Workbooks(l_vactiveworkbookname).Activate
Else
MsgBox gvc_WORKBOOKMESSAGE, vbInformation
Exit Sub
End If

'Checking for Temp Work Sheet in Active WorkBook
For l_i = 1 To Sheets.Count
If StrComp(Sheets(l_i).Name, gvc_TEMPWORKSHEETNAME, vbTextCompare) = 0 Then
GoTo Temp
End If
If StrComp(Sheets(l_i).Name, gv_DataSheetName, vbTextCompare) = 0 Then
GoTo DataSheet
End If
Next
GoTo continue

Temp:

Sheets(gvc_TEMPWORKSHEETNAME).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
l_vactiveworkbookname = ActiveWorkbook.Name
ActiveSheet.Paste
Application.CutCopyMode = False

continue:

Sheets(1).Activate
Range("A2").Activate
l_vactrow = ActiveCell.Row
Application.DisplayAlerts = False
' Deleting All worksheets except sheet1 in WorkBook
If ActiveWorkbook.Sheets.Count > 1 Then
For l_vscount = ActiveWorkbook.Sheets.Count To 1 Step -1
If StrComp(Sheets(l_vscount).Name, "sheet1", vbTextCompare) <> 0 Then
Sheets(l_vscount).Delete
End If
Next
End If
' Changing the Name of Sheet1 to Data
Sheets("Sheet1").Name = gv_DataSheetName

GoTo continue1

DataSheet:

Sheets(1).Activate
Range("A2").Select
'gv_vactrow = ActiveCell.Row
Application.DisplayAlerts = False

' Deleting All worksheets except sheet1 in WorkBook
If ActiveWorkbook.Sheets.Count > 1 Then
For l_vscount = ActiveWorkbook.Sheets.Count To 1 Step -1
If StrComp(Sheets(l_vscount).Name, gv_DataSheetName, vbTextCompare) <> 0 Then
Sheets(l_vscount).Delete
End If
Next
End If

continue1:

Call ToggleInteractiveChartToolbars(False)

Set ExPersonal = Excel.Application
With ExPersonal
.Workbooks.Open gv_personalfilename
l_ffound = False
For l_sheetcount = 1 To .Sheets.Count
If StrComp(.Sheets(l_sheetcount).Name, _
gvc_CHARTSTYLEINDICATOR & gv_ChartStyle, vbTextCompare) = 0 Then
l_ffound = True
GoTo DisplayChart
End If
Next
End With
If Not l_ffound Then
Set ExPersonal = Excel.Application
With ExPersonal
.Workbooks.Open gv_groupfilename
l_ffound = False
For l_sheetcount = 1 To .Sheets.Count
If StrComp(.Sheets(l_sheetcount).Name, _
gvc_CHARTSTYLEINDICATOR & gv_ChartStyle, vbTextCompare) = 0 Then
l_ffound = True
GoTo DisplayChart
End If
Next
End With
End If
If Not l_ffound Then
Application.ScreenUpdating = True
Set ExPersonal = Nothing
Exit Sub
End If

DisplayChart:

Sheets(l_sheetcount).Activate
If Sheets(l_sheetcount).Type = -4167 Then
ActiveSheet.ChartObjects(1).Activate
End If
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows(l_vactiveworkbookname).Activate
ActiveSheet.Paste
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=gv_ChartSheetName
' Add a new chart Style worksheet
Sheets.Add
ActiveSheet.Name = gv_ChartStyleSheetName
ActiveSheet.Paste
ActiveChart.SetSourceData Source:=Sheets(gv_ChartStyleSheetName).Range("A1:T20")

Sheets(gv_DataSheetName).Select

Call FindLastRowLastColumn(gv_DataSheetName)

' Checking for Empty Cells
gv_stcolumn = 66 ' start from "B"
gv_vactcolumn = ConvColNo2Alpha(gv_stcolumn - 64)
For l_j = 1 To gvc_CHARTDATAMAXCOLS
If StrComp(Sheets(gv_DataSheetName).Range(gv_vactcolumn & 1).Text, _
gvc_LASTCOLUMNINDICATOR, vbTextCompare) = 0 Then
gv_vactcolumn = ConvColNo2Alpha(gv_stcolumn - 64 - 1)
Exit For
End If
gv_stcolumn = gv_stcolumn + 1
gv_vactcolumn = ConvColNo2Alpha(gv_stcolumn - 64)
Next l_j

Sheets(gv_ChartSheetName).Activate
ActiveChart.SetSourceData Source:=Sheets(gv_DataSheetName).Range("A1" & ":" & gv_vactcolumn & gv_lastcolno), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet


For l_cnt = 1 To Application.Workbooks.Count
If StrComp(Application.Workbooks(l_cnt).FullName, _
gv_personalfilename, vbTextCompare) = 0 Then
Application.Workbooks(l_cnt).Close
Exit For
End If
Next
For l_cnt = 1 To Application.Workbooks.Count
If StrComp(Application.Workbooks(l_cnt).FullName, _
gv_groupfilename, vbTextCompare) = 0 Then
Application.Workbooks(l_cnt).Close
Exit For
End If
Next
' Setting Properties to ActiveChart
Call StartRowCommentsCapture '// Comments Displaying
With ActiveChart
.SizeWithWindow = True
.ChartTitle.Text = gv_ChartTitle
End With
Charts(gv_ChartSheetName).Move after:=Sheets(gv_DataSheetName)
Call ToggleScrollText(gv_InteractiveChart)
Call SearchforDateTextbox
On Error Resume Next
ActiveChart.PlotArea.Select
On Error GoTo 0
ActiveChart.ChartArea.Select
Set ExPersonal = Nothing
Application.ScreenUpdating = True
End Sub