Create a new sheet when closing the excel file

[nikolaybbelchev88]

Hello all,

When I close the excel file I would like to copy all the information from the first sheet and paste it in a new one with name, the user who did the changes, and this new sheet to be hidden.

Thanks in advance for your help!

Niki

 

[Yongle]

Re: How to create a new sheet when closing the excel file

Welcome to the forum
- do not place in a standard module

Only works if placed in ThisWorbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sh As String:   sh = Application.UserName
    On Error Resume Next
    Application.DisplayAlerts = False
        Sheets(sh).Delete
    Application.DisplayAlerts = True
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = sh
        .Visible = False
    End With
End Sub

 

[nikolaybbelchev88]

Re: How to create a new sheet when closing the excel file

Hello Yongle,

Thank you very much for your fast response!
The code is working perfect!
I would like to ask if we can modify it a bit in a way that we can store in a new invisible sheets(with user name) if the file is opened by different users.

Thanks ones again for your support!

Niki

 

[Yongle]

Re: How to create a new sheet when closing the excel file

I am confused by your question
- the code already takes a copy of the first sheet, names it "users name" and hides it

Can you explain what you are trying to achieve

What do you want to happen
- when user Paul opens the workbook ?
- when user Paul closes the workbook ?

What do you want to happen
- when user Jane opens the workbook ?
- when user Jane closes the workbook ?

In particular do Paul & Jane want to see their own previous worksheet when the workbook is opened by them?

thanks

 

[nikolaybbelchev88]

Re: How to create a new sheet when closing the excel file

Let me share the results from a test that I did with two users. I will use the same names as above:

Paul opened the file, did some changes and then the first sheet is copied to a new invisible one with username with the closing/save button which is perfect.
After that Jane opened the same file - the second sheet is not named Paul anymore(have the same name as sheet1) and it is visible. In the same time the code create new invisible third sheet named Jane when close/save it.

My question is - it is possible to create new sheet every time with name depending of the user who close/save the file and all new sheets to be invisible.
The idea is only one let`s call it master user to be able to see when someone do any changes in the file.

Hope that I explain my logic in the a good way.

Thanks for your help!

 

[Yongle]

Re: How to create a new sheet when closing the excel file

How many sheets with Paul's name

Paul opened file yesterday and new sheet created with name Paul
Paul opens file again today What happens?
- does he modify sheet "Paul"? or a new sheet?
- is sheet "Paul" deleted and new sheet "Paul" inserted
- is new sheet inserted every time Paul closes the file
- "Paul 001", "Paul 002", "Paul 003" etc

 

[nikolaybbelchev88]

Re: How to create a new sheet when closing the excel file

It will be great if we can create an invisible new sheet for every user that open and then close the file and all of them to stay invisible
-if for example Paul open and close the file multiple times let`s do it "Paul001", "Paul002" etc
-if Jane also open the file - "Jane001", "Jane002", "Jane003 etc

Thanks for your help!