G
Guest
With help from the experts here I have come up with the following routine
that creates a copy of the open workbook then performs calculations and saves
the data then opens the original workbook and paste in the data and then
closes and deletes the created workbook.
The problem is that when it gets to "Workbooks(sName).Close
SaveChanges:=False" in the Sub DeleteTempWB() it stops and shows a run time
error #9 "script out of range". Yet when I run DeleteTempWB from within the
workbook it works fine. I put the routine in differant areas and I get the
same. If I include another routine/procedure from the workbook, they work
fine. Any ideas would will be appreciated.
The code is
Sub ChangeToEvenProcedure()
Application.Run "CreateTempWB" - creates a copy w/saveas
Application.Run "ChangeToEven" - does the calculations and copies 12 cells
Application.Run "OpenOriginal" - opens the original and pastes in the data
Application.Run "DeleteTempWB" - close and delete the copy
Sub OpenOriginal()
sName = ThisWorkbook.FullName
sName = Left(sName, Len(sName) - 5) & ".xls"
Workbooks.Open sName
Sheets("Sheet1").Select
Range("C31").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Sub DeleteTempWB()
sName = ThisWorkbook.Name
sName = Left(sName, Len(sName) - 4) & "X.xls"
Workbooks(sName).Close SaveChanges:=False
Kill ThisWorkbook.Path & "\" & sName
End Sub
that creates a copy of the open workbook then performs calculations and saves
the data then opens the original workbook and paste in the data and then
closes and deletes the created workbook.
The problem is that when it gets to "Workbooks(sName).Close
SaveChanges:=False" in the Sub DeleteTempWB() it stops and shows a run time
error #9 "script out of range". Yet when I run DeleteTempWB from within the
workbook it works fine. I put the routine in differant areas and I get the
same. If I include another routine/procedure from the workbook, they work
fine. Any ideas would will be appreciated.
The code is
Sub ChangeToEvenProcedure()
Application.Run "CreateTempWB" - creates a copy w/saveas
Application.Run "ChangeToEven" - does the calculations and copies 12 cells
Application.Run "OpenOriginal" - opens the original and pastes in the data
Application.Run "DeleteTempWB" - close and delete the copy
Sub OpenOriginal()
sName = ThisWorkbook.FullName
sName = Left(sName, Len(sName) - 5) & ".xls"
Workbooks.Open sName
Sheets("Sheet1").Select
Range("C31").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Sub DeleteTempWB()
sName = ThisWorkbook.Name
sName = Left(sName, Len(sName) - 4) & "X.xls"
Workbooks(sName).Close SaveChanges:=False
Kill ThisWorkbook.Path & "\" & sName
End Sub