Problem with add a sheet to workbook macro

I have a workbook where I have a button to add a sheet, this works fine except for a formula that the macro edits on every sheet so we can have a running total quantity for the project. The problems occurs if the user changes the name of the last sheet in the workbook, then adds a sheet then the formula gets a reference error. The sheets are named 1 , 2 ,3 and so on, but it is nice to be able to change tha name of the sheet to the date.

Here is the code that Im have problems with

Please Login or Register  to view this content.

is there a code that would always reference the the previous sheet or I know that every sheet has 2 names a permanent sheet name that is Sheet1 Sheet2 and so on and the name that is shown on the tab at the bottom of the workbook, is there a way I can reference the permanent sheet name that would be a variable because I always need this formula to go to the previous page.

Any help is appreciated.
Thanks

Hi westonkw,

You can reference the sheets using their index number rather than their code name or tab name, like so:

Please Login or Register  to view this content.

This points to the sheet that is 1 sheet to the left of the activesheet. Change "ws.Index - 1" to "ws.Index - 3" or whatever you need.