Change file name

[Guest]

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

 

[J.E. McGimpsey]

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.

 

[Guest]

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

 

[J.E. McGimpsey]

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.