Thread: Subscript out of range error - save copy error

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

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


--
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=551468
>
>

Thanks, the Var2 variable was not being properly stored so the filename did not exist, thanks again.