Excel Help

[Hussein_Mohamed]

Dears,

is it applicable to do the below with excel:

if i have an excel master data updated on a daily basis and the file including alot of projects, is there any way to seperate the sheets with project name and the date of update automated when save and close the master sheet.

Thanks in advance for your usual support

[#2]

@Hussein_Mohamed 

There are many unanswered questions regarding your concerns.

What exactly do you want to accomplish?

If you want the whole thing finished, I recommend commissioning it.

 Here you can get help with parts of your project, such as a formula... but not complete the entire workbook.

Questions about master sheet, what is meant when the master sheet is closed?

Is it workbooks or worksheets?

However, it is desirable if you include more detailed information, information about the problem, about your Excel version, operating system, storage medium, etc.

Thank you for your understanding and patience.

[#3]

Thank you for your response hereunder my reply
What exactly do you want to accomplish?
i want to separate the workbook into sheets to be saved with sheet name and date of separation when i save and close the worksheet in defined location

Questions about master sheet, what is meant when the master sheet is closed?

Is it workbooks or worksheets?
i mean the workbook

[#4 · Accepted solution]

@Hussein_Mohamed 

You can achieve this with a VBA (Visual Basic for Applications) macro in Excel. The following code saves each sheet as an individual workbook in a defined location with the sheet name and the current date appended to the filename. In the example, you will find an approach.

This macro will run when the workbook is closed:

Open the Visual Basic for Applications editor by pressing Alt + F11, and then insert this code in the workbook's module.

Vba code (is untested):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Dim savePath As String
    Dim wb As Workbook
    Dim fileName As String
    Dim currentDate As String

    ' Define the folder path where you want to save the separated workbooks
    savePath = "C:\Your\Folder\Path\" ' Update with your desired path

    ' Create the folder if it doesn't exist
    If Dir(savePath, vbDirectory) = "" Then
        MkDir savePath
    End If

    For Each ws In ThisWorkbook.Worksheets
        ' Create a new workbook for each worksheet
        Set wb = Workbooks.Add
        ws.Copy Before:=wb.Sheets(1)
        
        ' Get the current date to append to the filename
        currentDate = Format(Now(), "yyyy-mm-dd")
        
        ' Form the filename with sheet name and date
        fileName = savePath & ws.Name & "_" & currentDate & ".xlsx"
        
        ' Save the new workbook with the formatted name and close it
        wb.SaveAs fileName
        wb.Close SaveChanges:=False
    Next ws
End Sub

This code will trigger when the workbook is closed. It loops through each sheet, creates a new workbook, copies the sheet into that new workbook, and saves it in the defined location with the sheet name and the current date.

Remember to change savePath = "C:\Your\Folder\Path\" to the path where you want to save the separate workbooks. This script will create a new file for each sheet upon closing the workbook.

AI was used to support the text.

VBA functionality is not available or possible in all Excel versions. It may also depend on where it is stored. However, some information was requested that you did not provide, so my help is unfortunately limited due to a lack of information.

But still hope it helped you .

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.

[#5]

thanks alot it works well with me, i have added time to the mentioned code

[#6]

Thank you for your feedback.
I am pleased that you have found a solution and are moving forward with your project.
I also wish you much success!

[#7]

@NikolinoDE 

Hi,  It is very close to what I am looking for. Is there any way we can save each sheet as a individual sheet in the same workbook. 

[#8]

@vemulnx 

If you want to save each sheet as an individual sheet in the same workbook instead of creating separate workbooks, you can modify the code accordingly.

Here is an updated version of the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Dim savePath As String
    Dim wb As Workbook
    Dim currentDate As String

    ' Define the folder path where you want to save the separated sheets
    savePath = "C:\Your\Folder\Path\" ' Update with your desired path

    ' Create the folder if it doesn't exist
    If Dir(savePath, vbDirectory) = "" Then
        MkDir savePath
    End If

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Create a new workbook
        Set wb = Workbooks.Add

        ' Copy the current worksheet to the new workbook
        ws.Copy Before:=wb.Sheets(1)

        ' Get the current date to append to the filename
        currentDate = Format(Now(), "yyyy-mm-dd")

        ' Save the new workbook with the formatted name and close it
        wb.SaveAs savePath & ws.Name & "_" & currentDate & ".xlsx"
        wb.Close SaveChanges:=False
    Next ws
End Sub

The code is untested, please backup your file first.

This modified code will create a new workbook for each sheet in the same workbook, copy the sheet into the new workbook, and save it with a filename based on the sheet name and the current date. Adjust the savePath variable to your desired folder path.