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.

[Norie]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[tompee29]

sorted, apologies for that

[TMS]

Awaiting code tags to be added.

[TMS]

@Norie: apologies, I didn't refresh the thread.

[Norie]

Which workbook do you want to refer to based on the value in a cell?

How many workbooks are open when you run the code?

Which workbook is the code in?

[tompee29]

Hi,

Ok, there's three workbooks altogether.

Control Workbook (DWOR Duplicator.xlsm, This contains all the code)

The Source Workbook is called DWOR Line 5 Week 15 Old .xlsm
The Destination Workbook is called DWOR Line 5 Week 15 2017.xlsm

The source and destination files are merely slave files and do not run any code, I basically need the source to duplicate specific ranges into the destination.

The aim is to have a nice frontend that a novice user can type the "week number" into say Cell "A1" and then click "run".

Cell A1 would then dynamically be pulled in by the code.

At the moment a novice would have to go into my code below and change the Week number to the latest Week number, i.e. Week 15 to Week 16.

 
Source Workbook (DWOR Line 5 Week 15 Old .xlsm)
Destination Workbook (DWOR Line 5 Week 15 2017.xlsm)

Hope this makes sense

Thanks,

Tom.

[Norie]

Why don't you use an inputbox?

Something like this perhaps.

Dim wkNum As String

    wkNum = InputBox("Please enter week number:")

    If wkNum <> "False" Then
        Set SourceWB = Workbooks("DWOR Line 5 Week " & wkNum & " Old .xlsm")
        Set DestinationWB = Workbooks("DWOR Line 5 Week " & wkNum & " 2017.xlsm")
    Else
        Exit Sub
    End If

    ' continue with rest of code

PS I'm assuming the source and destination workbooks are open.

PPS If they are open there could be other ways to refer to them, might not even need user input.

[tompee29]

Hmmm,

I like it. I didn't think of that route.

I'll get back to you and let you how I get on.

Thanks for your help Norie.

Tom.

[tompee29]

Hi Norie,

Your code was really helpful, I made a change though which now makes it do exactly what I originally wanted.

I substituted your input box request to get the wkNum, with the code: wkNum = Sheets("Start").Range("D8").Value.

Sub CopyDWOR()

Dim SourceWB As Workbook
Dim DestinationWB As Workbook

    Dim wkNum As String

     wkNum = Sheets("Start").Range("D8").Value
    
    If wkNum <> "False" Then
        Set SourceWB = Workbooks("DWOR Line 5 Week " & wkNum & " Old .xlsm")
        Set DestinationWB = Workbooks("DWOR Line 5 Week " & wkNum & " 2017.xlsm")
    Else
        Exit Sub
    End If

' Run rest of code

End Sub

Thanks again for your help

Regards,

Tom.