Want to bypass clipboard. Object var not set error. 2

[ColdDay]

Having trouble with Object Variable or With Variable not set.

Will have sheet with different months records. Need to filter, then copy filtered records to a sheet named either "Calendar2" or "PayPeriod2", depending on dates selected. I can get the filtering done, but no luck on the copying. Wanting to use Copy Destination to avoid the clipboard. If there is a better or easier way to accomplish this, I'm open to suggestions.

Sub DateFilterTimeSheetRecords()
Dim StartDate As Date
Dim EndDate As Date
Dim DateRangeType As String 'either a C or P for naming new sheet

Dim MonthEntries As Worksheet 'has all entries for numerous months
Dim LastRow As Long
Dim DatesSelected As Range

Dim CalendarSheet As Worksheet
Dim PayPeriodSheet As Worksheet
'-----------------------------------------

Sheets("MonthEntries").Select 'sheet with all records, no matter month
SortByDateForFiltering 'Different proc.  Sorts on col E.

'still need code to cancel out if desired.

'Calendar or PayPeriod records selected? 
'Enter either C or P.
DateRangeType = Application.InputBox("Enter either a C for Calendar or a " _
           & "P for Pay Period.")

'add a new sheet and rename it either "Calendar" or "PayPeriod".
Worksheets.Add
'code to rename just added sheet. will need sheet after filtering and selecting records.
Select Case DateRangeType
    Case Is = "C"
    ActiveSheet.Name = "Calendar2"
    Case Is = "P"
    ActiveSheet.Name = "PayPeriod2"
End Select

'-----Now get date ranges----------------------------
StartDate = Application.InputBox("Enter the Start Date in MM-DD-YYYY format")

EndDate = Application.InputBox("Enter the End Date in MM-DD-YYYY format")
    
Sheets("MonthEntries").Activate

ActiveSheet.Range("A1").End(xlDown).AutoFilter Field:=5, _
        Criteria1:=">=" & StartDate, _
        Criteria2:="<=" & EndDate
'is filtering correctly,

'determine rows to copy. did filter correctly.
LastRow = Sheets("MonthEntries").Cells.SpecialCells(xlCellTypeVisible).Row
Set DatesSelected = Sheets("MonthEntries").Range("A2:A" & LastRow)
'not sure if working to here. Value in LastRow is "1"

'----------------------------------------------
'need to select the new sheet (named either Calendar2 or PayPeriod2) _
    and paste records to it. Wanting to bypass the clipboard for _
    efficiency.
    
'Not getting past following Select Case. error msg of "Object Variable _
    or With Block variable not set".
Select Case DateRangeType
    Case Is = "C"
DatesSelected.EntireRow.Copy CalendarSheet.Cells(Rows.Count, 1).End(xlUp)(2)

    Case Is = "P"
DatesSelected.EntireRow.Copy PayPeriodSheet.Cells(Rows.Count, 1).End(xlUp)(2)

End Select

Stop

Columns("A:F").AutoFit
Range("A1").Select
Stop
'------------------------------------------------
MonthEntriesSheetAutoFilterOff 'different proc called.

End Sub

Hoping my post formatted correctly. Thanks.

Bill

 

[SkipVought]

Hi,

You never Set your worksheet objects in your code!

 

[PHV]

Select Case DateRangeType
    Case Is = "C"
DatesSelected.EntireRow.Copy Sheets("Calendar2").Cells(Rows.Count, 1).End(xlUp)

    Case Is = "P"
DatesSelected.EntireRow.Copy Sheets("PayPeriod2").Cells(Rows.Count, 1).End(xlUp)

End Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886