Create a new workbook with each new row

Question

Hello All,I'm looking to link and create a new Excel workbook whenever I create a new row.&nbsp;For example;&nbsp;Whenever I create a new row I want excel to make a new workbook that is saved on the internal server.The name of this workbook will need to contain the information in cells D &amp; E.A hyperlink on cell C will need to open this workbook.&nbsp;Reason for this seperate workbook is so that information on a project can be stored in this workbook &amp; the original overview file can be kept clean with only the essential information.&nbsp;I can understand that this question might be unclear. If you need any more info, please feel free to ask!&nbsp;&nbsp;Already thanks for the help&nbsp;Kind regards,Bram&nbsp;

nikolinode · Answer

BramSteenackers&nbsp;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\" &amp; projectName &amp; "_" &amp; Format(Now, "yyyy-mm-dd_hh-mm-ss") &amp; ".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.&nbsp;My answers are voluntary and without guarantee!&nbsp;Hope this will help you.Was the answer useful? Mark as best response and Like it!This will help all forum participants.

Forum Discussion

Create a new workbook with each new row

Share

Resources