Referencing Active Sheet

[Freddy]

I am trying to create a new more efficient form for some employees but really
struggling getting to grips with the code.

My main worksheet contains a table of costs and various lists that are
manually entered by the user

There is a macro button at the bottom I am using to make a summary of this
spreadsheet and paste it in a new sheet ready to be exported.

I am trying to figure out how to store the name of the sheet with the macro
button to be used as a reference in my SUMIFS function.

The reason being that over time there will be many 'original' cost sheets
and the way the code is now it will always refer back to the very first one
and likewise when calculating the SUMIFS.

Would you have any ideas?

Your help would be greatly appreciated

Code so far is as follows:

Sub Summary_Table()

'
' Summary_Table Macro
'
'
'THIS WAS TO STORE THE NAME OF THE SHEET CONTAINING THE
'MACRO BUTTON AND TO BE USED LATER FOR CHANGING THE WORKSHEET
'NAME USED IN THE SUMIF FUNCTION

Dim wksSummary As Worksheet
Set wksSummary = ActiveSheet.Name

'THIS SECTION WILL PASTE TABLE TEMPLATE INTO NEW SUMMARY WORKSHEET

Dim wksNew As Worksheet
Sheets("Template").Visible = True
Set wksNew = Sheets.Add(After:=Sheets(Sheets.Count))
Sheets("Template").Cells.Copy wksNew.Range("A1")
Sheets("Template").Select
ActiveWindow.SelectedSheets.Visible = False

' THIS SELECTS THE 1ST CELL AND INPUTS THE SUMIF FUNCTION FOR THE TEMPLATE

Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(S2974_1!R30C11:R39C11, S2974_1!R30C6:R39C6, RC2,
S2974_1!R30C12:R39C12, R3C)"
Range("C5").Select
Selection.AutoFill Destination:=Range("C5:W5"), Type:=xlFillDefault
Range("C5:W5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A1").Select

' THIS SECTION WILL ASK YOU TO RENAME YOUR SUMMARY WORKSHEET

ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Table 1"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Please Name
Your Summary Sheet")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0

'THIS SECTION OF THE MACRO WILL LOOK THROUGH THE LIST OF SITE TYPES AND
REMOVES ALL ROWS WITH A TOTAL COST OF 0

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
Firstrow = 5
Lastrow = 466
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "X")
If Not IsError(.Value) Then
If .Value = 0 Then .EntireRow.Delete
End If
End With
Next Lrow
End With

'THIS SECTION WILL DISPLAY THE COMPLETION CONFIRMATION BOX

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
If MsgBox(prompt:="Your summary is now complete", Buttons:=vbOK,
Title:="Summary Completed") = vbOK Then Exit Sub

End Sub

 

[Zone]

Freddy,
If you want to use the activesheet's name, dim wksSummary as a string:

Dim wksSummary As String
wksSummary = ActiveSheet.Name

Then you should be able to use it in your formula:

ActiveCell.FormulaR1C1 = _
"=SUMIFS(" & wksSummary & "!R30C11:R39C11, " _
& wksSummary & "!R30C6:R39C6, RC2, " _
& wksSummary & "!R30C12:R39C12, R3C)"

I haven't tested this, but it should work fine.
James