Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Active Sheet
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing an active cell's content from another sheet. | Excel Worksheet Functions | |||
Referencing cell in active row | Excel Worksheet Functions | |||
Referencing Cells without active cell | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
referencing the active cell | Excel Programming |