VBA Cell reference instead of Workbook name reference

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. 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!

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:
The workbook will be open but I do not know how to put this much code into those 3 sections. There has to be a simpler way to reference the cell for the file name...
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How about
VBA Code:
Dim wkb As Workbook, wkbFrom As Workbook
Dim fromPath As String, Source_File As String

With Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List")
    fromPath = .Range("I5").Value
    Source_File = .Range("M1").Value & ".xls"
End With
If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Set wkb = ThisWorkbook

Set wkbFrom = Workbooks.Open(fromPath & Source_File)
 
Upvote 0
I'm sorry, I really am a novice with VBA. How would I incorporate this in those 3 sections of the code?
 
Upvote 0
Replace this part of your code, with the code I posted
VBA Code:
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
'HERE
Set wkbFrom = Workbooks.Open(fromPath & "OPS 041 Customer Concerns Rev 7.xls")
 
Upvote 0
What would I do with these 2 sections?

1.
VBA Code:
Range("G1:G13").Select
    Selection.Copy
Windows("OPS 041 Customer Concerns Rev 7.xls").Activate
Range("O2").Select

2.
Code:
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"
 
Upvote 0
This
VBA Code:
Windows("OPS 041 Customer Concerns Rev 7.xls").Activate
becomes
VBA Code:
wkbFrom.activate
and this
VBA Code:
Source_File = "OPS 041 Customer Concerns Rev 7.xls"
Should be as I showed in post#12
 
Upvote 0
If I replace:

Code:
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

With

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

Source_File = .Range("M1").Value & ".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

I get an error... Sorry, am very new to this.

Do I have to add this to the beginning?

Code:
Dim wkb As Workbook, wkbFrom As Workbook
Dim fromPath As String, Source_File As String

With Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List")
    fromPath = .Range("I5").Value
    Source_File = .Range("M1").Value & ".xls"
End With
 
Upvote 0
You need the workbook/sheet name as well, like
VBA Code:
Source_File = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("M1").Value & ".xls"
 
Upvote 0
Not always, depends on your system settings. I always include it for safety's, but on this occasion just copied what the OP had & didn't notice it wasn't used.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top