# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

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

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

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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:
gowflow
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.