Change file name #2

Hi, I've been searching for a solution to the following problem for more than a week.  I've posted it here a couple of times, and got some good ideas, but unfortunately they did not work.  The variable "myfolder1" has the correct value, followed by ".xls" as it should.

I'd be very thankful if someone could find my error.  This is the original question:


I am new to Exel, and i cannot find my error in the following script.

The error is:
Run time erroro '9'
Subscript out of range


and it always stops on the "windows(my folder1).activate" line

My goal it to be able to rename a file using a cell content, and still be able to run this macro.  I'm putting the cell content into a variable, the variable is set to the correct value, but I still get the error.

Below is an exact copy/paste.  I suspect there is a syntax error.  If anyone can you see what I am doing wrong, please let me know.

Thanks


Sub List_Req2()
Dim myfolder1 As String

Range("A3").Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Application.CutCopyMode = False

With ThisWorkbook
    myfolder1 = .Worksheets("hotline").Range("G3").Value & ".xls"
    Range("F19").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    Windows(myfolder1).Activate
    '  ABOVE IS THE LINE IT STOPS ON
    Sheets("Request for Service").Select
    Range("I13:J13").Select
    Selection.Copy
    Windows("list_Req.xls").Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
    Windows(myfolder1).Activate
    Application.CutCopyMode = False
    Sheets("Hotline").Select
    Range("G3").Select
    Selection.ClearContents
    End With
End Sub


0
anonymous (74722)
12/29/2003 2:11:08 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
294 Views

Similar Articles

[PageSpeed] 41

As often happens, using Selections and Windows rather than 
addressing Workbooks and Ranges directly makes the code confusing. 
Couple of questions:

1) In what workbook does this macro reside?
2) What are the ActiveWorkbook and Activesheet names when this macro 
is started?
3) What is the exact value of Thisworkbook's hotline!G3
4) Where does the hyperlink in cell F19 (on the sheet that is active 
when the macro starts) point to?

5) When you get the error, what are the exact names of the open 
windows?

A syntax error would be flagged before the macro runs. 
In article <88C1DBCB-2602-4F04-AE82-6E1BFC5766F3@microsoft.com>,
 "Peter" <anonymous@discussions.microsoft.com> wrote:

> Hi, I've been searching for a solution to the following problem for more than 
> a week.  I've posted it here a couple of times, and got some good ideas, but 
> unfortunately they did not work.  The variable "myfolder1" has the correct 
> value, followed by ".xls" as it should.
> 
> I'd be very thankful if someone could find my error.  This is the original 
> question:
> 
> 
> I am new to Exel, and i cannot find my error in the following script.
> 
> The error is:
> Run time erroro '9'
> Subscript out of range
> 
> 
> and it always stops on the "windows(my folder1).activate" line
> 
> My goal it to be able to rename a file using a cell content, and still be 
> able to run this macro.  I'm putting the cell content into a variable, the 
> variable is set to the correct value, but I still get the error.
> 
> Below is an exact copy/paste.  I suspect there is a syntax error.  If anyone 
> can you see what I am doing wrong, please let me know.
> 
> Thanks
> 
> 
> Sub List_Req2()
> Dim myfolder1 As String
> 
> Range("A3").Select
> Selection.Copy
> Range("G3").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
>     False, Transpose:=False
> Application.CutCopyMode = False
> 
> With ThisWorkbook
>     myfolder1 = .Worksheets("hotline").Range("G3").Value & ".xls"
>     Range("F19").Select
>     Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
>     Rows("1:1").Select
>     Selection.Insert Shift:=xlDown
>     Range("A1").Select
>     Windows(myfolder1).Activate
>     '  ABOVE IS THE LINE IT STOPS ON
>     Sheets("Request for Service").Select
>     Range("I13:J13").Select
>     Selection.Copy
>     Windows("list_Req.xls").Activate
>     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
>         False, Transpose:=False
>     Range("A1").Select
>     Windows(myfolder1).Activate
>     Application.CutCopyMode = False
>     Sheets("Hotline").Select
>     Range("G3").Select
>     Selection.ClearContents
>     End With
> End Sub
> 
>
0
jemcgimpsey (6723)
12/29/2003 4:36:10 PM
1)The macro is in "Follow_up.xls

2)When I run the macro, Only "Follow_up.xls" is open, and on the first sheet named "Hotline

3)hotline!A3 contains a formula that gathers text from other cells.  hotline!G3 is a blank cell that i transfer the value of A3 into using a "paste special"
At the end of the macro, I clear this value.  This part works, because the variable "myfolder1" has a value of the sum of A3, plus ".xls"
After I get the error, I click on "Debug".  From there if I hover over the variable "myfolder", it displays the correct value (in this case, abcd.xls, because the sum of A3 is abcd)

4) F19 on the hotline sheet, points to another worksheet called list.xls

5)When the macro errors out, list.xls is open, and is the ActiveWorkbook and Activesheet, and a line has been inserted

This macro works if I replace "myfolder1" by the file name "Follow_up.xls".  But my goal is to be able to run this macro after the filename has changed. eg:Follow_up2.xls

0
anonymous (74722)
12/29/2003 6:21:31 PM
If A3 refers to the workbook that the macro is in, then your macro 
reduces to:

   Public Sub List_Req2()
       With ThisWorkbook
           .Worksheets("Hotline").Range("F19").Hyperlinks(1).Follow _
                 NewWindow:=False, AddHistory:=True
           ActiveSheet.Rows(1).Insert Shift:=xlDown
           ActiveSheet.Range("A1").Resize(1, 2).Value = _
                .Sheets("Request for Service").Range("I13:J13").Value
           .Activate
       End With
    End Sub

No need to track the Window name - just use the ThisWorkbook object.

I assumed that you answer to #4 should have been "list_req.xls", 
since you don't open that file elsewhere.


In article <5D496F81-6966-440F-9A31-6DAB9F9F79C0@microsoft.com>,
 "Peter" <anonymous@discussions.microsoft.com> wrote:

> 1)The macro is in "Follow_up.xls"
> 
> 2)When I run the macro, Only "Follow_up.xls" is open, and on the first sheet 
> named "Hotline"
> 
> 3)hotline!A3 contains a formula that gathers text from other cells.  
> hotline!G3 is a blank cell that i transfer the value of A3 into using a 
> "paste special".
> At the end of the macro, I clear this value.  This part works, because the 
> variable "myfolder1" has a value of the sum of A3, plus ".xls".
> After I get the error, I click on "Debug".  From there if I hover over the 
> variable "myfolder", it displays the correct value (in this case, abcd.xls, 
> because the sum of A3 is abcd).
> 
> 4) F19 on the hotline sheet, points to another worksheet called list.xls.
> 
> 5)When the macro errors out, list.xls is open, and is the ActiveWorkbook and 
> Activesheet, and a line has been inserted.
> 
> This macro works if I replace "myfolder1" by the file name "Follow_up.xls".  
> But my goal is to be able to run this macro after the filename has changed. 
> eg:Follow_up2.xls.
>
0
jemcgimpsey (6723)
12/29/2003 7:01:40 PM
Reply:

Similar Artilces: