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