How do I automate the addition of a sheet into a workbook for calculations?

Question

I have a 1/4 audit process that I would like to autamate, I have a standarized audit sheet that I will complete for each project, appox 45. I will need to have a dashboard and charts update when I add the sheet or new audit to my work book. How can I do this so that when I add a sheet that all information gets pull from the new sheet to the dashboard and subsequently the chatrs. I have the dashboard and charts corralating when I manually entering the new sheet to the dashboard. Any suggestions on how to automate the addition of a sheet to the workbook?

Answer 1

Here are two macros that will allow for the addition of a blank new sheet or copy a specific sheet and add it to the workbook. You question is a little vague as to 'adding information and charts'.  You indicate you need to 'add a sheet that all information gets pulled to the dashboard and charts'.  Yet, you state that when you manually enter a new sheet the dashboard and charts correlate.

Anyway, this will get the process started.  Please edit your question with more info if you wish to move beyond this.

Change your "Sheet" references below to suit your needs before copying to the clipboard. You will also have to rename a copied sheet, if necessary, as the new copied sheet name will be, for example, 'Sheet1 (2)'

Sub AddSheet()

Sheets("Sheet1").Select

Sheets.Add

End Sub

Sub CopySheet()

Sheets("Sheet1").Select

Sheets("Sheet1").Copy After:=Sheets(1)

End Sub

You can either attach these macros to commandbuttons, or setup a keyboard shortcut to do call them with keystroke combinations.

Answer 2

You need to have a macro. Please elaborate little more on how the new sheet is linked to your dashboard and then to the charts. If possible email me a sample file and I would return you the solution file after working on it.