willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 888
- Office Version
- 365
- Platform
- Windows
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!
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!
VBA Code:
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
Last edited: