Thread: Macro question "file name"

I have a question on the file name.

I constructed a macro that basically extracts data from one spreadsheet and puts it on a new spreadsheet in a different format.

In order for my macros to function the orginal spreadsheet must be named Modified RQFR.xls

My colleagues often rename the sheet to a different name so they know what data is on it. I've instructed them to rename the sheet back when extracting the data.

My question is if there's a way around this so that the orginal sheet (the one that contains the macros) can named anything and have the macro still function?

Here's the macro.

Code:

Sub SetupQuote()
Set NewBook = Workbooks.Add
With NewBook
.Title = "Empowerment Quote"
.SaveAs Filename:="Empowerment Quote.xls"
End With

Windows("Modified RQFR.xls").Activate
Range("F5").Select
Selection.Copy
Windows("Empowerment Quote.xls").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

instead of windows(file_name) put windows(1) , windows(2) .. window(1) is always the active window

Code:

Sub SetupQuote()
Set NewBook = Workbooks.Add
With NewBook
.Title = "Empowerment Quote"
.SaveAs Filename:="Empowerment Quote.xls"
End With

Windows(2).Activate
Range("F5").Select
Selection.Copy
Windows(2).Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Assuming that Modified RQFR.xls (or whatever its name is) is open to the proper sheet before this code executes, then

Code:

Sub SetupQuote()
    Dim wks As Worksheet
    Dim wkbNew As Workbook
    
    Set wks = ActiveSheet
    
    Set wkbNew = Workbooks.Add
    With wkbNew
        .Title = "Empowerment Quote"
        .SaveAs Filename:="Empowerment Quote.xls"
    End With

    ActiveSheet.Range("A4").Value = wks.Range("F5").Value

    ....