# Insert row suming above numbers

#### windwardmi

##### Board Regular
I am using the below code to pull rows meeting a certain date criteria. The rows contain columns that I need to sum. Example: No. of regular hours and overtime hours.

How can I add a row after the right rows are pulled with the sum() formula in the column where I need it?

Code:
``````Sub TimeSheet()
'
' TimeSheet Macro
'
' Keyboard Shortcut: Ctrl+t
'

Dim WS As Worksheet
Dim rightsheet As String
Dim StartDate As Date, EndDate As Date

StartDate = Application.InputBox("Start Date?")
EndDate = Application.InputBox("End Date?")
'ActiveSheet.PageSetup.CenterHeader = "&12" & rightsheet & Chr(10) & ActiveSheet.Name & Chr(13) & rightsheet & Chr(10) & (StartDate) & " To " & (EndDate)
ActiveSheet.PageSetup.RightHeader = "&12" & Chr(13) & rightsheet & Chr(10) & (StartDate) & " To " & (EndDate)

rightsheet = Application.InputBox("Employee Last Name?")

Sheets(rightsheet).Select
Selection.AutoFilter
ActiveSheet.Range("\$A\$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
'IgnorePrintAreas:=False
End Sub``````

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Tim_Excel_

##### Well-known Member
Assuming the two values are in rows 1 and 2
Code:
``````Rows(2).EntireRow.Insert
acll = ActiveCell

With Workbooks("[B]NAME[/B]").Worksheets([B]NUMBER[/B])
For each c in .Rows(1)
.Range(acll).Copy
.Range(acll).Offset(2,0).Paste
.Range(acll).Offset(1,0).Copy
.Range(acll).Offset(3,0).Paste
Next
End With``````

This should work.. I hope

Last edited:

#### windwardmi

##### Board Regular
The value rows can be numerous and unknown.

I'm totally confused on your code.

I would like to add a row right after the Autofiltered rows to sum a few columns like I, J, K, L, M, N

#### windwardmi

##### Board Regular
I'm trying to add this code to get the sum but it seems to freeze up. What am I missing and can't see.

Code:
``````Sub GetSum()
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 2).End(x1Up).Row
ThisWorkbook.Sheets("Sheet1").Range("b" & lastRow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("b:b" & lastRow))

End Sub``````

#### Tim_Excel_

##### Well-known Member
I must've completely misread what you wrote, because my code doesn't sum up anything.. oops.
From what I get you wrote (and forgive me I am wrong, it is late) you have values in two rows, which need to be summed up in a third row.

Again I am assuming the values are in row 1 and 2, so change to your liking
Code:
``````Rows(2).EntireRow.Insert
acll = ActiveCell

With Workbooks("[B]NAME[/B]").Worksheets([B]NUMBER[/B])
For each c in .Rows(1)
val1 = .Range(acll)
val2 = .Range(acll).Offset(1,0)
.Range(acll).Offset(3,0).Value = [FONT=Consolas]Application.Sum(Range([/FONT] val1, val2)
Next
End With``````

Try this

#### windwardmi

##### Board Regular
Tim,

Let me try to describe my workbook goal.

It is an employee time tracking project I want to share with my employees on SharePoint. On their assigned worksheet the employee will input time information by using a userfom they can pop up.

This userform asks for date, project no., location, actual hours worked and number of hours worked(RT, OT, TT)

When done inputting the employee clicks the enter button. The data will be inputted on next blank row of his worksheet in the correct column. This is repeated for every project worked and every day.

So now we have a record of our employee's time.

Then every 2 weeks when we do payroll I have the aforementioned code pull a report for each employee for a set period (2 weeks)

That 2 week report can be 14 lines (one for each day) or many more lines due to the employee working on multiple projects each day.

With that said I need to have totals of the number of hours worked for each time column. I.e. Regular Time, Over Time, etc.

I'm looking to add code to my original autofilter code to find the last line of data for that 2 week perion and sum the columns for Regular Time, OT etc. Presently columns I through P.

I fond this code on YOUTUBE link: https://youtu.be/_0Vcnb3xdOM and I can not seem to get it to work like it does online.

Code:
``````Sub GetSum()
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 2).End(x1Up).Row
ThisWorkbook.Sheets("Sheet1").Range("b" & lastRow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("b:b" & lastRow))

End Sub``````

I hope this helps you all.

Replies
12
Views
1K
Replies
0
Views
1K
Replies
4
Views
815
Replies
3
Views
248
Replies
6
Views
250

1,191,120
Messages
5,984,762
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back