Select rows of data and total some columns

[windwardmi]

I have the following VBA code that works most of the way:

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

ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

   
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

ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

    Range("E65536").End(xlUp).Offset(2, 0).Value = "TOTAL"
    
    Range("I65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("I2:I" & Cells.SpecialCells(xlLastCell).Row))
    Range("j65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("j2:j" & Cells.SpecialCells(xlLastCell).Row))
 
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
'IgnorePrintAreas:=False
End Sub

This part of the code does not work. Why is that? It works by itself in a separate procedure. What am I missing.

    Range("E65536").End(xlUp).Offset(2, 0).Value = "TOTAL"
    
    Range("I65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("I2:I" & Cells.SpecialCells(xlLastCell).Row))
    Range("j65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("j2:j" & Cells.SpecialCells(xlLastCell).Row))

 

[windwardmi]

Ok, if I activate the .PrintOut in the code it allows the following lines of code to work but the total is the total worksheet columns not just the rows in the range of the requested start and end dates. How do I get the total of only the rows requested?

Range("E65536").End(xlUp).Offset(2, 0).Value = "TOTAL"
    
    Range("I65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("I2:I" & Cells.SpecialCells(xlLastCell).Row))
    Range("j65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("j2:j" & Cells.SpecialCells(xlLastCell).Row))

 

[MARK858]

Deleted as the OP has posted more info.

 

[MARK858]

How do I get the total of only the rows requested?

If you mean only the visible cells then use SUBTOTAL rather than SUM with 109 as the subtotal criteria.

 

[windwardmi]

Yes I had a pasting error. Past should have been following:

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

ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)
    Range("E65536").End(xlUp).Offset(2, 0).Value = "TOTAL"
    
    Range("I65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("I2:I" & Cells.SpecialCells(xlLastCell).Row))
    Range("j65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("j2:j" & Cells.SpecialCells(xlLastCell).Row))
 
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
'IgnorePrintAreas:=False
End Sub

In this form the following does not create a sum line of the columns requested. If I activate ActiveWindow.SelectedSheets.PrintOut we do get a sum line of the columns requested but it is the total or sum of the original sheet not that of the requested dates that are printing.

Range("I65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("I2:I" & Cells.SpecialCells(xlLastCell).Row))    Range("j65536").End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Sum(Range("j2:j" & Cells.SpecialCells(xlLastCell).Row))

 

[windwardmi]

It should be .subtotal not .sum? What do you mean by "with 109 as the subtotal criteria

 

[MARK858]

    Range("E" & Rows.Count).End(xlUp).Offset(2, 0).Value = "TOTAL"
    
    Range("I" & Rows.Count).End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Subtotal(109, Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row))
    Range("J" & Rows.Count).End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Subtotal(109, Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row))

 

[windwardmi]

When pasted in the code it does not give me a subtotal line(2nd row below requested dates) when filtered. I unfilter and the subtotal line is their corrupting original data.

 

[MARK858]

Post the code as you have it as there is nothing in the code I posted that puts the result in any different position to the code you posted (unless your data goes past line 65536).

 

[windwardmi]

Code as follows: By the way enjoy the coming of the New Year

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

ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(StartDate), _
    Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)
    Range("E" & Rows.Count).End(xlUp).Offset(2, 0).Value = "TOTAL"
    
    Range("I" & Rows.Count).End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Subtotal(109, Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row))
    Range("J" & Rows.Count).End(xlUp).Offset(2, 0).Value = _
        WorksheetFunction.Subtotal(109, Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row))

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub