VBA Cell reference instead of Workbook name reference

[willow1985]

I have the below code that references a workbook called: "OPS 041 Customer Concerns Rev 7"

However this workbook name may change in the future and instead of updating the code every time there is a change it would be easier to update a spreadsheet.

Is there a way to reference a specific cell for the workbook name?

The Macro is running from Workbook: Customer Concern - Warranty Request Log TEST

I would like to replace worbook name: "OPS 041 Customer Concerns Rev 7" with this reference instead: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("M1")
Where M1 Contains the text: OPS 041 Customer Concerns Rev 7

Is there a way to do this? I get an error if I just replace one with the other.

Thank you!

Sub Complete_File()
'
' Complete_File Macro
'

'
Msg = "Your CC File will be finalized," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes

Sheets("CC Database").Select
Range(Cells(Selection.Row, 16), Cells(Selection.Row, 16)).Select
Selection.Copy
    Sheets("List").Select
    Range("G13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Call Transfer_CC

'Open file Named: "OPS 041 Customer Concerns Rev 7.xls" from directory: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
Dim wkb As Workbook, wkbFrom As Workbook
Dim fromPath As String

fromPath = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")

If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Set wkb = ThisWorkbook
Set wkbFrom = Workbooks.Open(fromPath & “OPS 041 Customer Concerns Rev 7.xls”)

Windows("Customer Concern - Warranty Request Log TEST.xlsm").Activate
Range("G1:G13").Select
    Selection.Copy
Windows(“OPS 041 Customer Concerns Rev 7.xls”).Activate
Range("O2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Save as Name: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("G1") 'in same directory aka Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5"):

Dim Path As String
Dim filename As String
Path = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
filename = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("G1")

ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

Kill Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I7")

   Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

Private Sub Transfer_CC()
Dim FSO
Dim Source_File As String
Dim Source_Folder As String
Dim Destination_Folder As String

Source_File = “OPS 041 Customer Concerns Rev 7.xls”
Source_Folder = "\\DAVINCI-1\USERDATA\carla.fisher\Desktop\Customer Concerns\"
Destination_Folder = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile (Source_Folder & Source_File), Destination_Folder, True

End Sub

 

[willow1985]

Worked perfect! Thank you again Fluff ?

 

[Fluff]

You're welcome & thanks for the feedback