Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro crash when trying to copy sheets - why?

Status
Not open for further replies.

WmHumphrey

Technical User
Mar 20, 2002
3
US
Can anyone illuminate me as to why my macro is hanging when trying to copy a chart sheet (a 3D contour plot), and the worksheet which contains its data, to a new workbook? The chart sheet also contains several text boxes whose text will be modified programmatically later in the macro.

Here's the code... I checked the locals window and all variables are declared and have valid values as of the line before the one that hangs.

' Create new plot template
Workbooks.Add Template:=strTemplateDir & "\" & strPlotTemplateName
'Adjust plot template name to new workbook's
strPlotTemplateName = Workbooks(Workbooks.Count).Name
' Create new workbook to build data summary in; save in working directory
Workbooks.Add Template:=xlWBATWorksheet
Workbooks(Workbooks.Count).SaveAs FileName:=strWorkDir & "\" _
& strWorkFileName

'Loop through rows in DB file
Do
'Open raw data file for current row in DB file
strRawDataFile = Workbooks(strDBFileName).Sheets(1).Cells(intDBRow,10).Value
Workbooks.OpenText FileName:=strRawDataFile, DataType:=xlDelimited, _
Tab:=True, origin:=xlWindows
'Strip path from raw data file name
strRawDataFile = Workbooks(Workbooks.Count).Name
'Read pattern type from DB file
strPatternType = Workbooks(strDBFileName).Sheets(1).Cells(intDBRow, 11).Value
strPatternTemplateName = strPatternType & " Pattern"

'Copy appropriate pattern and raw data form to temp workbook
'*** HERE'S THE LINE THAT HANGS MY MACHINE ***
Workbooks(strPlotTemplateName).Sheets(Array(strPatternTemplateName, _
"Raw Data")).Copy Before:=Workbooks(strWorkFileName).Sheets(1)

'... (more code, modifying the newly copied "Raw Data" sheet and the text boxes
'on the chart, follows but the macro never gets past the line above!)

intDBRow = intDBRow + 1
Loop Until Workbooks(strDBFileName).Sheets(1).Cells(intDBRow,1).Value = ""

If anyone has ANY ideas, please help!
I've got Excel 97 SR-1 on a 400MHz P-II with 128 MB and Windows 95-B (and no control over updates).

Thanks in advance!
Bill
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top