Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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


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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Subscript out of range error - save copy error


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


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subscript out of range error - save copy error bg18461[_15_] Excel Programming 1 June 13th 06 04:36 PM
In multiple sheet copy error subscript out of range ?? HELP Eddy Stan Excel Programming 4 March 22nd 06 04:54 AM
Type Mismatch error & subscript out of range error Jeff Wright[_2_] Excel Programming 3 May 14th 05 07:14 PM
subscript out of range error Darren Excel Programming 3 November 24th 04 03:38 PM
Subscript out of range error Tom Ogilvy Excel Programming 0 December 10th 03 12:48 AM


All times are GMT +1. The time now is 08:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"