asked on How to trigger a macro on new sheet creation in excel
Hello
I would like to create a macro that will trigger when I create a new sheet in excel (clicking the + on the sheets status bar.
That macro will basically increase a counter that.i have in sheet1 (the template that I will create).
I’m in need of this because I’m creating an invoice template whereby whenever I create a new invoice (new sheet) the invoice number will get automatically increased by 1 (counter).
I of course would welcome another solution if there is a better / more reliable one.
Thanks
Pierre Ammoun
I would like to create a macro that will trigger when I create a new sheet in excel (clicking the + on the sheets status bar.
That macro will basically increase a counter that.i have in sheet1 (the template that I will create).
I’m in need of this because I’m creating an invoice template whereby whenever I create a new invoice (new sheet) the invoice number will get automatically increased by 1 (counter).
I of course would welcome another solution if there is a better / more reliable one.
Thanks
Pierre Ammoun
Microsoft OfficeMicrosoft ExcelVBA
Last Comment
Ryan Chong
ASKER
Supposing that my cell is in A1 (that contains the Counter.)
How do I assign in a new sheet the A1 value to another sheet ?
So each time I create a new sheet, I need to add 1 to the A1 in the template , and take that number and put it in the New Sheet.
so that each time I create a new sheet , I have a new number... 1, 2, 3, 4, 5, etc,,
How do I assign in a new sheet the A1 value to another sheet ?
So each time I create a new sheet, I need to add 1 to the A1 in the template , and take that number and put it in the New Sheet.
so that each time I create a new sheet , I have a new number... 1, 2, 3, 4, 5, etc,,
you may try:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
Worksheets("Main").Cells(1, 2) = Worksheets.Count - 1
Set ws = Sh
ws.Cells(1, 1) = Worksheets("Main").Cells(1, 2)
End Sub
ASKER
Dear,
Thank you for the info.
I tried it but it is not working.
I am attaching the example file I am working on.
Care to check if out for me ?
Thanks
Example.xlsm
Thank you for the info.
I tried it but it is not working.
I am attaching the example file I am working on.
Care to check if out for me ?
Thanks
Example.xlsm
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you so much.
But I still have a problem. The way you designed it , the counter is based on the number of sheets..
So If I delete a sheet and create it again, I will get the same number as the one deleted...
What I want is a counter that keeps adding...So I will start with 1 , and it will increments by 1 even if delete a sheet...
So I might end up with only 3 sheets, but their numbers could be 10,11,12...
Thank you very much
But I still have a problem. The way you designed it , the counter is based on the number of sheets..
So If I delete a sheet and create it again, I will get the same number as the one deleted...
What I want is a counter that keeps adding...So I will start with 1 , and it will increments by 1 even if delete a sheet...
So I might end up with only 3 sheets, but their numbers could be 10,11,12...
Thank you very much
ASKER
found it !
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
Worksheets("Main").Cells(1
Set ws = Sh
ws.Cells(1, 1) = Worksheets("Main").Cells(1
End Sub
Thanks !!
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
Worksheets("Main").Cells(1
Set ws = Sh
ws.Cells(1, 1) = Worksheets("Main").Cells(1
End Sub
Thanks !!
ASKER
Thank you !
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
a quick update.. If i wanted the same functionality but when I copy a sheet instead of creating a new sheet ?
If i wanted the same functionality but when I copy a sheet instead of creating a new sheet ?It seems that there is no event for copy worksheet, I got to do a more deeper research if this is available or is there an alternative way to detect that.
Open in new window