TurnpikeGate
08-05-2008, 10:55 AM
I need to extract dates from a pivot table( the worksheet has a couple pivot tables in it) and put them in another worksheet. I am doing a Find command to look for a date but i would like it to look for a whole month and select that month from the list and copy it into the other work book. As well i am not ever sure how to get the find to be specific to one column. here is the Code i am working from:
Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long
Sheets("RNK PIVOT TABLES").Select
strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)
'Cancelled
If strdate = "False" Then Exit Sub
strdate = Format(strdate, "Short Date")
On Error Resume Next
Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
On Error GoTo 0
If rCell Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then Run "FindDate":
End If
End Sub
Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long
Sheets("RNK PIVOT TABLES").Select
strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)
'Cancelled
If strdate = "False" Then Exit Sub
strdate = Format(strdate, "Short Date")
On Error Resume Next
Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
On Error GoTo 0
If rCell Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then Run "FindDate":
End If
End Sub