[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 229

# Gather a range of numbers and report total on another sheet

Good evening experts,

I need to add up the rows in column G of worksheet "Labor Totals Temp" and cause the total to be placed at location C25 of Worksheet "Controls". The issue is that this totaling and reporting needs to occur at the end of the csv cleanup. of "Labor Totals Temp" because during stage 4 the "Labor Totals Temp sheet is deleted.

Any help would be appreciated. I've included a sample Workbook with code.

Regards,
Brian

Master-Phase-3---Cosmetics-and-D.xlsm
0
RedstoneIT
• 5
• 4
• 2
• +1
2 Solutions

Author Commented:
another issue is that this column will vary based on imported data.
0

Commented:
I'm old school, so I changed around how this looked a little, and it seemed to make sense to me.  Maybe I accidently fixed it?
Example.xlsx
0

Author Commented:
Um, Script, I think you posted this on the wrong question...... I wasn't working on spreadsheets of baby stuff
0

Commented:
Yes, sorry I got those two mixed up.  I'll have a look
0

Solutions ConsultantCommented:
This function will return the value of the rage. Just insert the function in the workbook and use some thing like

Dim total as double
total  = getTotal()

To get and store the total at the point you need it.

Michael
Function getTotal() As Double
Dim lastRow As Long
With Sheets("Labor Totals Temp")
lastRow = .Range("G" & Rows.Count).End(xlUp).Row
getTotal = Application.WorksheetFunction.Sum(.Range("G2:" & "G" & lastRow))
End With
End Function
0

Author Commented:
ok and how would I place the total into a cell in workbook Control ?

0

Commented:
I would just have a cell that is always :
=SUM('Labor Totals Temp'!G:G)

and then do a quick macro/vba that does a copy paste such as:
Sub MakePermLaborTotals()
'' MakePermLaborTotals Macro
Sheets("The Sheet your Formula Cell is on").Select
Range("Your Formula Cell Here").Select
Selection.Copy
Sheets("Controls").Select
Range("C25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Hope that helps!

-SA
0

Commented:
Here is your new sub Labour Cleanup incorporating your request to put total of Col G of Labor Totals Temp in C25 of sheet Controls.
pls try it and let me know.

I have also attached the file for your trial.
gowflow
Sub LaborCleanUp()

Dim rData As Range

Application.ScreenUpdating = False

'Get total for Col G in Labor Total Temp and place value
'in Col C25 of sheets Control
Dim MaxRow As Long, I As Long, Tot As Double

With Sheets("Labor Totals Temp")
MaxRow = .Range("G" & .Rows.Count).End(xlUp).Row
For I = 1 To MaxRow
Tot = Tot + Val(.Range("G" & I).Value)
Next I
End With
Sheets("Controls").Range("C25").Value = Tot

With Sheets("Labor Totals Temp")
.Range("A1").Resize(, 8).Formula = "=""Field"" & column()"
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=""
.Range("A1").AutoFilter Field:=4, Criteria1:="0", Operator:=xlOr, Criteria2:=""
With .AutoFilter.Range
On Error Resume Next
Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rData Is Nothing Then
rData.EntireRow.Delete
End If
End With
.AutoFilterMode = False
.Rows(1).Clear ' clear header row

End With

Set rData = Nothing

Application.ScreenUpdating = True

ActiveWorkbook.Sheets("Labor Totals Temp").Columns("A").Delete

Application.Goto reference:=Sheets("Controls").[a1]
ThisWorkbook.Worksheets("Controls").Cells(12, 14).Value = "Deleting Filename rows from Labor Totals Temp."

Application.ScreenUpdating = False

With Sheets("Labor Totals Temp")
.Range("A1").Resize(, 8).Formula = "=""Field"" & column()"
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, Criteria2:=""
With .AutoFilter.Range
On Error Resume Next
Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rData Is Nothing Then
rData.EntireRow.Delete
End If
End With
.AutoFilterMode = False
.Rows(1).Clear ' clear header row

End With

Set rData = Nothing

Application.ScreenUpdating = True

Application.ScreenUpdating = False

With Sheets("Labor Totals Temp")
.Range("A1").Resize(, 8).Formula = "=""Field"" & column()"
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=7, Criteria1:="0", Operator:=xlOr, Criteria2:=""
With .AutoFilter.Range
On Error Resume Next
Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rData Is Nothing Then
rData.EntireRow.Delete
End If
End With
.AutoFilterMode = False
.Rows(1).Clear ' clear header row

End With

Set rData = Nothing

Application.ScreenUpdating = True

Dim cel As Range
Sheets("Labor Totals Temp").Activate
For Each cel In ActiveSheet.UsedRange.Cells
If cel.Value = 0 Then cel.ClearContents
Next cel

Application.Goto reference:=Sheets("Controls").[a1]
ThisWorkbook.Worksheets("Controls").Cells(13, 14).Value = "Cleanup of Labor Totals Temp sheets completed."
End Sub
Master-Phase-3---Cosmetics-and-D.xlsm
0

Author Commented:
Script,

I did the =SUM('Labor Totals Temp'!G1:G64000)

The issue is that when the Labor Totals Temp sheet is deleted in the next step, the total will go away. It needs to be held in the C:25 Cell
0

Commented:
Right,

As per the other comments as well, at some point you will need to invoke something. On mine it's a simple cut and paste values.  Others are using more complex methods.  But once you run the cut and paste values function then it no longer matters what the sheet says.  Or if it's gone entirely.  It will just be a number in C25 on controls.
0

Author Commented:
Thank you all for your help!
0

Commented:
Tks for the grade and glad we could help
gowflow
0

## Featured Post

• 5
• 4
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.