Code to set workbook name (dynamic)

[tompee29]

Hi,

Is there a way in which I can get the “Set Workbooks name” to come from a cell value within excel, instead of setting the filename from the vba code as shown below:

I want the operator to not have to go into the code, so entering the name in a sheet is preferred then running a macro is preferred.


Example code:

 Sub CopyDWOR()

 Dim SourceWB As Workbook
 Dim DestinationWB As Workbook

 Set SourceWB = Workbooks("DWOR Line 5 Week 15 Old .xlsm")
 Set DestinationWB = Workbooks("DWOR Line 5 Week 15 2017.xlsm")


 ' Sunday AM

 ' Copy & Paste SKUs
 SourceWB.Sheets("Sun AM").Range("C43:D57").Copy
 DestinationWB.Sheets("Sun AM").Range("C43").PasteSpecial

 ' Copy & Paste Plan Cases
 SourceWB.Sheets("Sun AM").Range("G43:R57").Copy
 DestinationWB.Sheets("Sun AM").Range("G43").PasteSpecial

 End Sub

Many thanks in advance.
Tom.