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

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

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
Asked:
RedstoneIT
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
RedstoneITAuthor Commented:
another issue is that this column will vary based on imported data.
0
 
ScriptAddictCommented:
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
 
RedstoneITAuthor Commented:
Um, Script, I think you posted this on the wrong question...... I wasn't working on spreadsheets of baby stuff
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ScriptAddictCommented:
Yes, sorry I got those two mixed up.  I'll have a look
0
 
Michael FowlerSolutions 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

Open in new window

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

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

Open in new window


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

Open in new window


Hope that helps!  

-SA
0
 
gowflowCommented:
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")
    ' Add headers
    .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")
    ' Add headers
    .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")
    ' Add headers
    .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

Open in new window

Master-Phase-3---Cosmetics-and-D.xlsm
0
 
RedstoneITAuthor 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
 
ScriptAddictCommented:
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
 
RedstoneITAuthor Commented:
Thank you all for your help!
0
 
gowflowCommented:
Tks for the grade and glad we could help
gowflow
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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