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:

Please Login or Register  to view this content.

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]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/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)

[TMS]

Awaiting code tags to be added.

[TMS]

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

[tompee29]

sorted, apologies for that

[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.

Please Login or Register  to view this content.

Hope this makes sense

Thanks,

Tom.

[Norie]

Why don't you use an inputbox?

Something like this perhaps.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Thanks again for your help

Regards,

Tom.