Excel VBA - Copy and Rename File

[af96]

Hello everyone,

I am completely new to macros. I have seen many threads related to this topic, however, I have not been successful, so my apologies for duplicating a question.
I wish to create a macro which will open a template work book and save it as a specific file name. I have created a template file and another file with the custom names.

This is the thread that seems to have answered my query, but I was unsuccessful: Macro to Copy and Rename Files in Different Directory.

Please could anyone help?

Many thanks in advance.

 

[Wookiee]

Sub OneForAF96()
'Crafted by Wookiee at MrExcel.com

Dim wkbTemplate   As Workbook
Dim strNewFile    As String
Dim strSavePath   As String

'Open Template File
Workbooks.Open Filename:="X:\Path\YourTemplate.xlsx", ReadOnly:=True
Set wkbTemplate = ActiveWorkbook

'Assumes You Are Saving New File In Same Directory
'Change If You Want To Save Elsewhere
strSavePath = wkbTemplate.Path

'Get New Filename
strNewFile = InputBox("Enter new file name:")

wkbTemplate.SaveAs Filename:=strSavePath & "\" & strNewFile

End Sub

 

[af96]

Hello, thank you for this. Apologies for my silly question, but I'm not entirely sure what to put here:

strNewFile = InputBox("Enter new file name:")

As I need to pull this information from a separate sheet which lists the custom names.

 

[Wookiee]

Hello, thank you for this. Apologies for my silly question, but I'm not entirely sure what to put here:

strNewFile = InputBox("Enter new file name:")

As I need to pull this information from a separate sheet which lists the custom names.

Apologies, that was meant as a place-holder where you could revise the macro logic to specify however you wanted to assign the file name.

Here's a rough example:

strNewFile = Sheets("Separate Sheet").Range("A420").Value