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.
- 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:UsersaattwoodDesktopTest"
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.
- 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:
- 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.
- Then save and close the new files/workbooks without me having to close whatever of the new files/workbooks that remained opened.
I suggest changing the first code:
Sub SaveAsRegularWorkbook()
Dim wb As Workbook
Dim Path As String
Dim Name1 as string, Name2 as string
Name1="0019516" : Name2="0019519"
Set wb = ThisWorkbook
Path = "C:UsersaattwoodDesktopTest"
Application.DisplayAlerts = False
Application.EnableEvents = False
wb.SaveAs Filename:=Path & "CPX." & Name1 & ".xlsx", FileFormat:=51
wb.worksheets("SheetName").Range("D3").Value=Name1
wb.SaveAs Filename:=Path & "CPX." & Name2 & ".xlsx", FileFormat:=51
wb.worksheets("SheetName").Range("D3").Value=Name2
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Parts in red are changed.
