Forum Discussion
Create a new workbook with each new row
Below is a sample VBA code that you can use to create a new workbook with each new row and save it with the information from cells D and E. Additionally, it adds a hyperlink in cell C to open the newly created workbook:
Vba is untested and is only an example, please backup your file bevor you using the code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newRow As Range
Dim newWorkbook As Workbook
Dim savePath As String
Dim projectName As String
' Define the range where new rows are added
Set newRow = Intersect(Target, Me.Rows(Me.Rows.Count))
' Check if a new row is added
If Not newRow Is Nothing Then
' Get the project name from cell D in the new row
projectName = newRow.Offset(0, 3).Value
' Generate a file name based on the project name and current date/time
savePath = "\\internal_server\" & projectName & "_" & Format(Now, "yyyy-mm-dd_hh-mm-ss") & ".xlsx"
' Create a new workbook
Set newWorkbook = Workbooks.Add
' Save the new workbook with the generated file name
newWorkbook.SaveAs Filename:=savePath
' Create a hyperlink in cell C of the new row to open the newly created workbook
newRow.Offset(0, 2).Hyperlinks.Add Anchor:=newRow.Offset(0, 2), Address:=savePath, TextToDisplay:="Open Workbook"
' Close the new workbook without saving changes (to keep it empty)
newWorkbook.Close SaveChanges:=False
End If
End SubWith this code, whenever you add a new row in that worksheet, a new workbook will be created and saved on the internal server. The name of the workbook will be based on the information in cells D and E of the new row, and a hyperlink will be added in cell C to open the newly created workbook.
Make sure to replace "\internal_server" with the actual path where you want to save the new workbooks. Also, ensure that you have appropriate permissions to access and save files to that location. The text and code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.