File name change

[Guest]

I am very new with Exel

The following works, even though it might not be pretty.

But what I want to do is to be able to run this after changing the file name Follow_up.xls to, for example, 1234.xls.
The value 1234 is the sum of A3, so I put it in a variable - myFolder1 -
How do I enter it into the - Windows("Follow_up.xls").Activate - line, in the place of "Follow_up.xls"

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
Range("F19").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Windows("Follow_up.xls").Activate
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("Follow_up.xls").Activate
Application.CutCopyMode = False
Sheets("Hotline").Select
Range("G3").Select
Selection.ClearContents
End With
End Sub

Thanks for any help, and Happy Holidays

 

[J.E. McGimpsey]

If I understand correctly what you're trying to do, use

Windows(myFolder1).Activate

 

[Guest]

If i run it as i sent it to you, it works
If i replace Windows(Follow_up.xls).Activat
with Windows(myFolder1).Activat
I get an error

 

[J.E. McGimpsey]

And that error is....??

How are you assigning myFolder1 a value? are you adding ".xls" to
your filename?

 

[Guest]

As I said, I'm very new with Exel, and new with variables. Thanks for your patience

The error is
Run time ettro '9
Subscript out of rang

I'm pretty sure my problem is just with my syntax
How do I add ".xls" to "(myFolder1)

I have tried every thing but the right way

 

[J.E. McGimpsey]

Either modify the variable:

myFolder1 = Range("A3").Value & ".xls"

or modify the Activate statment:

Windows(myFolder1 & ".xls").Activate

If you still get a subscript out of range error, it's because A3
contains text that does not match your workbook name.

 

[Guest]

Thanks for your help, and I'm sorry to bother you again.

I think I entered everything the way you said, but I am still getting the same error,
and it still stops on the same ACTIVATE line

This is an exact copy/paste. Can you see what I am doing wrong?

Thanks again


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

the only thing I see is that you're assigning the value of

hotline!G3

to myfolder1.

Are you sure G3 on sheet hotline is populated? Your first section
does a paste special to the ActiveSheet, which may or may not be
hotline.

When you get the error and click on Debug, what does the variable
myfolder1 contain?