Subscript out of range error - save copy error
 

This is a snippet from a function I developed, when I run the code i
halts on the 'starred' line below. I think that it has something to d
with the way the filenames are referenced and its not seeing th
filename when it looks for it. Is there any other way I can write thi
code to avoid the subscript errors.

Dim wb1 As Workbook
Dim WBorigName As String
Dim WBname As String
Dim WBname2 As String
Application.ScreenUpdating = False

Var2 = Range("B2").Value

Dim strdate As String
strdate = Format(Now, "mm-dd-yy_h-mm-ss")

WBorigName = "OEF-" & Var2 & ".xls"
WBname = "S:\DPLN\Relrpts\IRSHistory\OEF2006\Entered OEF\" & "OEF-"
Var2 & "_" & strdate & ".xls"
WBname2 = "S:\DPLN\Relrpts\IRSHistory\OEF2006\" & "OEF-" & Var2
".xls"
***Workbooks(WBorigName).SaveCopyAs WBname
Workbooks(WBorigName).Close SaveChanges:=False
Kill (WBname2)
Application.ScreenUpdating = True
End I

--
bg1846
-----------------------------------------------------------------------
bg18461's Profile: http://www.excelforum.com/member.php...nfo&userid=887
View this thread: http://www.excelforum.com/showthread.php?threadid=55146

Subscript out of range error - save copy error
 

Since you are building a workbook name by concatenating some information
together, are you sure you have a workbook open that has that name?

Dim bk as Workbook
Dim strdate As String
strdate = Format(Now, "mm-dd-yy_h-mm-ss")

WBorigName = "OEF-" & Var2 & ".xls"
On Error Resume Next
set bk = Workbooks(WBorigName)
On Error goto 0
if bk is nothing then
msgbox wBorigName & " does not exits, quitting"
exit sub
End if

.. . . rest of your code.

--
Regards,
Tom Ogilvy




"bg18461" wrote:


This is a snippet from a function I developed, when I run the code it
halts on the 'starred' line below. I think that it has something to do
with the way the filenames are referenced and its not seeing the
filename when it looks for it. Is there any other way I can write this
code to avoid the subscript errors.

Dim wb1 As Workbook
Dim WBorigName As String
Dim WBname As String
Dim WBname2 As String
Application.ScreenUpdating = False

Var2 = Range("B2").Value

Dim strdate As String
strdate = Format(Now, "mm-dd-yy_h-mm-ss")

WBorigName = "OEF-" & Var2 & ".xls"
WBname = "S:\DPLN\Relrpts\IRSHistory\OEF2006\Entered OEF\" & "OEF-" &
Var2 & "_" & strdate & ".xls"
WBname2 = "S:\DPLN\Relrpts\IRSHistory\OEF2006\" & "OEF-" & Var2 &
".xls"
***Workbooks(WBorigName).SaveCopyAs WBname
Workbooks(WBorigName).Close SaveChanges:=False
Kill (WBname2)
Application.ScreenUpdating = True
End If


--
bg18461
------------------------------------------------------------------------
bg18461's Profile: http://www.excelforum.com/member.php...fo&userid=8877
View this thread: http://www.excelforum.com/showthread...hreadid=551463