Within a "Tracker" workbook I have coded for the user to create a folder and populate it with a selection of files (including other workbooks). One of the files is a "Memo" workbook, where the name of this workbook is derived from data in the "Tracker" workbook, i.e. "Memo FileReferenceFolder", where FileReferenceFolder is made up of "FileNumber-FileYear".
FileNumber would be a numerical value from 1 to x. from Cell "A1" in the "Tracker" workbook, and
FileYear would be the current year, i.e. 2024, which is derived from Cell "I1" in the "Tracker" workbook. Cell "I1" contains a full date, i.e. 16/02/2024.
So in the above example the file name would be "Memo 1-2024"
As can hopefully be seen from the below, the code Opens a workbook named "Memo", saves it as "Memo FileReferenceFolder", and closes the 'original' Memo workbook.
In the newly created "Memo FileReferenceFolder", workbook, Cell B11 is populated with a corresponding reference number, i.e. "FileNumber/FileYear", i.e. "1/2024", then that workbook is also closed.
Once all work is complete within the files contained within the created folder, a reviewer is notified to check the completed work. The reviewer would then enter their name and date onto the corresponding "Memo" workbook and save it.
Currently someone else then has to open the "Memo" document and manually input the reviewer name and reviewed date into the Tracker workbook, within the relevant Cells.
I am trying to automate this task, so that when the reviewer completes their review, they would execute a command etc. which would populate the "Tracker" workbook with their details and the review date.
Would it be a case of programming a button within the "Memo" workbook to vlookup the relevant reference number (1 to x) and drop the reviewer name and date into the corresponding Cell of the "Tracker" workbook?
I hope this makes sense....
FileNumber would be a numerical value from 1 to x. from Cell "A1" in the "Tracker" workbook, and
FileYear would be the current year, i.e. 2024, which is derived from Cell "I1" in the "Tracker" workbook. Cell "I1" contains a full date, i.e. 16/02/2024.
So in the above example the file name would be "Memo 1-2024"
As can hopefully be seen from the below, the code Opens a workbook named "Memo", saves it as "Memo FileReferenceFolder", and closes the 'original' Memo workbook.
In the newly created "Memo FileReferenceFolder", workbook, Cell B11 is populated with a corresponding reference number, i.e. "FileNumber/FileYear", i.e. "1/2024", then that workbook is also closed.
VBA Code:
Workbooks.Open "C:\TEMPLATE\Memo.xlsm"
Workbooks("Memo").SaveAs FileName:=BrowseForFolder & "Memo " & FileReferenceFolder & ".xlsm"
Workbooks("Memo.xlsm").Close
With Workbooks("Memo " & FileReferenceFolder & ".xlsm")
.Worksheets("Memo").Range("B11") = FileReference
.Close
End WithOnce all work is complete within the files contained within the created folder, a reviewer is notified to check the completed work. The reviewer would then enter their name and date onto the corresponding "Memo" workbook and save it.
Currently someone else then has to open the "Memo" document and manually input the reviewer name and reviewed date into the Tracker workbook, within the relevant Cells.
I am trying to automate this task, so that when the reviewer completes their review, they would execute a command etc. which would populate the "Tracker" workbook with their details and the review date.
Would it be a case of programming a button within the "Memo" workbook to vlookup the relevant reference number (1 to x) and drop the reviewer name and date into the corresponding Cell of the "Tracker" workbook?
I hope this makes sense....
