Copy and Paste (Hardcode) Formula as Value, then Save and Close the new Workbook/File|VBA & Macros|Excel Forum|My Online Training Hub

Hi

I have an Excel template that I need to publish for each project that I save as a separate file/workbook. The project number becomes the filename as well as the primary key in Cell D3 of my template so that all formulas in my template refers to the primary key (project number). I use the following formula to extract the project number from the filepath in Cell D3:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-6)

Furthermore, I have two pieces of VBA code (see below) to run in my template.

1. The first piece of code saves the File As and adopts the project name (new file/workbook) I specify in the code (I use two project numbers for this demonstration)

Sub SaveAsRegularWorkbook()

    Dim wb As Workbook

    Dim Path As String

    Set wb = ThisWorkbook

    Path = "C:\Users\aattwood\Desktop\Test\"

        Application.DisplayAlerts = False

    Application.EnableEvents = False

wb.SaveAs Filename:=Path & "CPX.0019516.xlsx", FileFormat:=51

wb.SaveAs Filename:=Path & "CPX.0019519.xlsx", FileFormat:=51

    Application.DisplayAlerts = True

    Application.EnableEvents = True

End Sub

This codes works well therefore I do not have a problem with it.

2. The second piece of code basically needs to copy the formula in Cell D3 and paste it as a value (hardcode) in the same cell.

Sub Macro1()

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

However, the issue I have with this piece of code is even though it does what it needs to do, it only hardcodes the formula in Cell D3 as a value for the last record/project number in the code and it automatically opens just this one file/workbook. The one above it or how many there are above it still retains the result in the form of the formula in Cell D3.

Please I would appreciate if anybody out there could improve the second piece of code for me to do the following:

1. Look at all the records/project numbers saved as new workbooks as per the first piece of code, then hardcodes the formula (result) in Cell D3 as a value of the new files/workbooks.
2. Then save and close the new files/workbooks without me having to close whatever of the new files/workbooks that remained opened.