Hi all. I have the following macro which when executed, this is what it does :
Step 1: A macro is run from "WorkBook 1". Once clicked, the macro would open a saved template, called "Template", from a defined location.
Step 2: An Input Box appears, prompting the user to insert a new name for a new workbook
Step 3: A new workbook, based on "Template", is created with the name inputted in step 2 above. E.g: the new name would be "Workbook 2". In summary, a copy of "Template" is produced and the name used in Step 2 -"Workbook 2"- is given to this new copy.
The code is the following and can be viewed below.
What I would like to do, if it is possible, is that I would like to copy some cells from another workbook into this newly created workbook.
Therefore, for example, this is how I'd like the macro to work:
Step 1: A macro is run from "WorkBook 1". Once clicked, the macro would open a saved template, called "Template", from a defined location.
Step 2: An Input Box appears, prompting the user to insert a new name for a new workbook
Step 3: A new workbook, based on "Template", is created with the name inputted in step 2 above. E.g: the new name would be "Workbook 2". In summary, a copy of "Template" is produced and the name used in Step 2 -"Workbook 2"- is given to this new copy.
Step 4: Some cells from "WorkBook 1" (identified in Step 1 above), are copied and pasted into the newly created "Workbook 2".
Step 5: Save and close Workbook 2
Up till now, the code caters for Step 1 to Step 3. But I would like to include Step 4 and Step 5. I had posed a similar question in the link below, but I was wondering if I could receive additional guidance:
VBA for Template and Copying Cells.
Can someone help me out if this is possible. Appreciate a lot.
Thanks everyone
Step 1: A macro is run from "WorkBook 1". Once clicked, the macro would open a saved template, called "Template", from a defined location.
Step 2: An Input Box appears, prompting the user to insert a new name for a new workbook
Step 3: A new workbook, based on "Template", is created with the name inputted in step 2 above. E.g: the new name would be "Workbook 2". In summary, a copy of "Template" is produced and the name used in Step 2 -"Workbook 2"- is given to this new copy.
The code is the following and can be viewed below.
Code:
[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub trial()
Dim wbNew As Workbook
Dim strTempPath As String
Dim strTempName As String
Dim strNewName As String
strTempPath = "C:\Users\User\Documents\Accounts\W&E\"
strTempName = "Template.xltm"
Set wbNew = Workbooks.Add(Template:=strTempPath & strTempName)
strNewName = InputBox("Please enter workbook's name:")
wbNew.SaveAs strTempPath & strNewName, xlOpenXMLWorkbookMacroEnabled
End Sub</code>[COLOR=#333333]What I would like to do, if it is possible, is that I would like to copy some cells from another workbook into this newly created workbook.
Therefore, for example, this is how I'd like the macro to work:
Step 1: A macro is run from "WorkBook 1". Once clicked, the macro would open a saved template, called "Template", from a defined location.
Step 2: An Input Box appears, prompting the user to insert a new name for a new workbook
Step 3: A new workbook, based on "Template", is created with the name inputted in step 2 above. E.g: the new name would be "Workbook 2". In summary, a copy of "Template" is produced and the name used in Step 2 -"Workbook 2"- is given to this new copy.
Step 4: Some cells from "WorkBook 1" (identified in Step 1 above), are copied and pasted into the newly created "Workbook 2".
Step 5: Save and close Workbook 2
Up till now, the code caters for Step 1 to Step 3. But I would like to include Step 4 and Step 5. I had posed a similar question in the link below, but I was wondering if I could receive additional guidance:
VBA for Template and Copying Cells.
Can someone help me out if this is possible. Appreciate a lot.
Thanks everyone
