Refering to te previous sheet - VBA

Hi i´m new on Excel VBA. On hard time i accomplished to modified/create/mix this routine for a button, but the only thing a could not make it work is when i create a new sheet (the code do that and allow you to give the new name of the sheet) the "calculus" that must be do it on the new sheet must be refenced on the last sheet. See the Code below, the XXXX´s should be the name of the previous sheet. So i ask it´s possible to do it? If yes how can i do it?

Sub Botão10_Clique()

Dim ActNm As String

Dim sWh As Long

sWh = ThisWorkbook.Sheets.Count

With ActiveWorkbook.Sheets

Worksheets("Dia 1").Copy after:=Worksheets(sWh)

ActiveWindow.View = xlNormalView
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.OnAction = "Botão10_Clique"
Range("E12:G12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[15]C[12]:R[25]C[12])+'XXXXX'!R12C5:R12C7"
Range("K8:L9").Select
ActiveCell.FormulaR1C1 = "='XXXXX'!R8C11:R9C12+1"
Range("M8:M9").Select
ActiveCell.FormulaR1C1 = "='XXXXX'!R8C13:R9C13+1"
Range("K12:M12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C[6]:R[-4]C[6])+'XXXXX'!R12C11:R12C13"
Range("F15:I15").Select
ActiveCell.FormulaR1C1 = "='XXXXX'!R15C6:R15C9+1"
Range("B15:E15").Select

End With

ActNm = ActiveSheet.Name

On Error Resume Next

ActiveSheet.Name = "C10:C18"

NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Proximo.")

If ActiveSheet.Name = ActNm Then GoTo NoName

On Error GoTo 0

End Sub

Code example

OK, the below code has been written straight here (not in VBA editor, so may have errors (but should demonstrate purpose):

Define the worksheets (old and new)
Work with the defined worksheets rather than activesheet.
Avoid all unnescessary selects (if you see ".select ... selection.xxx" delete that bit to bring the two lines together.

I hope this gets you back on track

Sub Botão10_Clique()
Dim ActNm As String
Dim sWh As Long

dim oldWS as worksheet: set oldWS = activesheet
sWh = ThisWorkbook.Sheets.Count

With ActiveWorkbook.Sheets
Worksheets("Dia 1").Copy after:=Worksheets(sWh)
dim newWS as worksheet: set newWS = activesheet

ActiveWindow.View = xlNormalView
newWS.Shapes.Range(Array("Button 1")).OnAction = "Botão10_Clique"
newWS.Range("E12:G12").FormulaR1C1 = "=SUM(R[15]C[12]:R[25]C[12])+'" & oldWS.name & "'!R12C5:R12C7"
newWS.Range("K8:L9").FormulaR1C1 = "='" & oldWS.name & "'!R8C11:R9C12+1"
newWS.Range("M8:M9").FormulaR1C1 = "='" & oldWS.name & "'!R8C13:R9C13+1"
newWS.Range("K12:M12").FormulaR1C1 = "=SUM(R[-9]C[6]:R[-4]C[6])+'" & oldWS.name & "'!R12C11:R12C13"
newWS.Range("F15:I15").FormulaR1C1 = "='" & oldWS.name & "'!R15C6:R15C9+1"
Range("B15:E15").Select

End With

ActNm = newWS.Name

On Error Resume Next

newWS.Name = "C10:C18"

NoName: If Err.Number = 1004 Then newWS.Name = InputBox("Proximo.")

If newWS.Name = ActNm Then GoTo NoName

On Error GoTo 0

End Sub

Copy template sheet and basing formulas on previous sheet

Just for fun, i thought of getting the first previous worksheet

' get the first previous worksheet
sWh = ActiveWorkbook.Sheets.Count
For i = sWh To 1 Step -1
With ActiveWorkbook.Sheets(i)
If .Type = xlWorksheet Then Set oldWS= ActiveWorkbook.Sheets(i): Exit For
End With
Next i