Find Range, highlight rows, copy to new sheet tab.

3/13/18 8:49:23
3/13/18 8:33:37
3/13/18 8:13:03
3/13/18 8:13:03
3/12/18 17:00:59
3/12/18 16:38:35
3/12/18 15:37:48

How would I use vba to capture only the 3/13/18 values and then select that ranges rows to copy to another sheet tab?  I would start at cell A2 and have it move down until it is no longer 3/13/18.  Because there are times in with the date... it causes a problem...  I later remove the times when I reformat them, but the times need to stay in for this part of the process.  Please advise and thanks.
RWayneHAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hmm. The following code will copy the data based on the first date entered in B7 (whatever it is). Does that work for you now?

Sub CopyPreviousDayData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long
Dim prevDate As Long

Application.ScreenUpdating = False

Set sws = Sheets("LogArchived")
sws.AutoFilterMode = False
slr = sws.Cells(Rows.Count, 2).End(xlUp).Row

sws.Range("M7:M" & slr).Formula = "=Int(B7)"
sws.Range("M7:M" & slr).NumberFormat = "General"
prevDate = Range("M7").Value

On Error Resume Next
Set dws = Sheets("Yesterday's Data")
dws.Cells.Clear
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Yesterday's Data"
End If

sws.Range("M6:M" & slr).AutoFilter field:=1, Criteria1:=prevDate
sws.Range("B6:L" & slr).SpecialCells(xlCellTypeVisible).Copy
dws.Range("A1").PasteSpecial xlPasteAll
dws.UsedRange.Columns.AutoFit
sws.ShowAllData
sws.Range("M6:M" & slr).ClearContents
sws.Range("B6:L6").AutoFilter
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
You can use Advanced Filter for this. It will filter the data and then can also copy to another sheet.

If the values are true Date & Time values then having the time in there shouldn't make a difference.

Dates are held as an integer serial number with day 1 being back on 1 Jan 1900, 13 March 2018 was 43172

Time is then stored as a decimal part to the same number; for example midday of that day would be 43172.5 but when formatted as Date & Time would show as 03/13/18 12:00

So, when filtering for 13 March you are looking for values >= to 43172 and <43173

If you upload a file with some sample data, I can show what I mean with Advanced Filter
0
 
RWayneHAuthor Commented:
So how would that look using vba and not using absolute references?  if the starting point is the same each time, like A1?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
It wouldn't use VBA at all, other than to refresh the filter rather than re-running from the data menu as and when required.

If the data was set as a table, then the Adv Filter can be set to look at the table rather than a fixed range.

Thinking about it, you could maybe use a Pivot Table to filter the dates and then just copy the results of the pivot.
0
 
RWayneHAuthor Commented:
Thanks for your feedback, but the requirement is to use this in vba...  I understand that a p-table could be used etc. but please re-read the question and advise.  Thanks.
0
 
RWayneHAuthor Commented:
making sure that all the rows are selected...
0
 
RWayneHAuthor Commented:
this is being inserted in a number of places in a much larger workbook.,..  I wanted to make it easy..
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe you can try something like this...
Sub CopyRowsWithSpecificDate()
Dim lr As Long, i As Long
Dim Rng As Range
Dim strDate As String

strDate = "03/13/18"
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
    If Int(Cells(i, 1)) = DateValue(strDate) Then
        If Rng Is Nothing Then
            Set Rng = Cells(i, 1)
        Else
            Set Rng = Union(Rng, Cells(i, 1))
        End If
    End If
Next i
If Not Rng Is Nothing Then
    Rng.EntireRow.Copy Sheet2.Range("A2")   'Copying the rows to Sheet2, change it as per your requirement.
End If
End Sub

Open in new window

0
 
RWayneHAuthor Commented:
So what happens when 3/19/2018 happens?  strDate will be different each day.  I can make the strDate be the ActiveCell, so whatever day it is will be the new day?  The way it is written it will do 3/18/2018 each time.  strDate needs to be dynamic.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can read strDate from the cell to make it dynamic like below...
Sub CopyRowsWithSpecificDate()
Dim lr As Long, i As Long
Dim Rng As Range
Dim strDate As Date

'Assuming the date criteria is in the cell AA1
strDate = Range("AA1").Value

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
    If Int(Cells(i, 1)) = strDate Then
        If Rng Is Nothing Then
            Set Rng = Cells(i, 1)
        Else
            Set Rng = Union(Rng, Cells(i, 1))
        End If
    End If
Next i
If Not Rng Is Nothing Then
    Rng.EntireRow.Copy Sheet2.Range("A2")   'Copying the rows to Sheet2, change it as per your requirement.
End If
End Sub

Open in new window

0
 
RWayneHAuthor Commented:
Code is failing on Ln11?  This dataset is sorted newest to oldest, so my starting active cell is B7.  Is that what is making it fail?
my lr = 443.  Should I just loop through the top few rows to find where it differs?  It will only go through may be a dozen rows before a different day is found...  ???  Not sure why it fails..
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It should not fail on that line unless the cell contains an error in it.
Are the dates returned by a formula? Is there any cell with error?
When code fails, click on debug on the error window and hover your mouse pointer over the variable i and then manually look at the cell, what value that cell contain?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If possible, can you upload the workbook in question? Just erase all the data except the column A (date field) and upload it here.
0
 
RWayneHAuthor Commented:
The date field is in column B and starts at row 7.  It will always be from the top of sheet down.  When copied to the new sheet tab, would like it to select cell A2 and paste..  Example attached.  Because this is yesterday's data, the macro should find and copy Rows 7 thru 17

Thanks.
CaptureRangeOfSameDatesWithTimes.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, give this a try.
The following code will insert a new sheet called "Yesterday's Data" if not already present in the workbook and copy the yesterday's data from the log sheet.
You may tweak the code to name it something else if you wish.
Sub CopyPreviousDayData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long

Application.ScreenUpdating = False

Set sws = Sheets("LogArchived")
If sws.FilterMode Then sws.ShowAllData
slr = sws.Cells(Rows.Count, 2).End(xlUp).Row

On Error Resume Next
Set dws = Sheets("Yesterday's Data")
dws.Cells.Clear
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Yesterday's Data"
End If

sws.Range("B6:L" & slr).AutoFilter field:=2, Criteria1:=xlFilterYesterday, Operator:=xlFilterDynamic
sws.Range("B6:L" & slr).SpecialCells(xlCellTypeVisible).Copy
dws.Range("A1").PasteSpecial xlPasteAll
dws.UsedRange.Columns.AutoFit
sws.ShowAllData
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
RWayneHAuthor Commented:
Having an issue with Ln21.  If today = Monday and we are updating, based on  xlFilterYesterday, that would be Sunday and the autofilter would not return anything (or a day is messing, vacation etc.).  Basically that autofilter needs to be set to whatever the value is since the last time ran, in this case it would be cell B7.  So I named, cell B7 "TargetDate" and tried.
sws.Range("B6:L" & slr).AutoFilter Field:=2, Criteria1:=TargetDate, Operator:=xlFilterDynamic

Open in new window



Which it does not like.... It basically needs to pull the value from cell B7 to autofilter by, instead of using: xlFilterYesterday.
How would Ln21 be rewritten to do this?
0
 
Rob HensonFinance AnalystCommented:
Have you pulled TargetDate in as a variable?

TargetDate = Range ("TargetDate").Value
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, give this a try...
The following code finds the previous working day dynamically.
Sub CopyPreviousDayData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long
Dim prevDate As Double

Application.ScreenUpdating = False

'Finding the previous working day
prevDate = WorksheetFunction.WorkDay(Date, -1)

Set sws = Sheets("LogArchived")
sws.AutoFilterMode = False
slr = sws.Cells(Rows.Count, 2).End(xlUp).Row

sws.Range("M7:M" & slr).Formula = "=Int(B7)"
sws.Range("M7:M" & slr).NumberFormat = "General"
On Error Resume Next
Set dws = Sheets("Yesterday's Data")
dws.Cells.Clear
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Yesterday's Data"
End If

sws.Range("M6:M" & slr).AutoFilter field:=1, Criteria1:=prevDate
sws.Range("B6:L" & slr).SpecialCells(xlCellTypeVisible).Copy
dws.Range("A1").PasteSpecial xlPasteAll
dws.UsedRange.Columns.AutoFit
sws.ShowAllData
sws.Range("M6:M" & slr).ClearContents
sws.Range("B6:L6").AutoFilter
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
RWayneHAuthor Commented:
Maybe I am not explaining myself clearly enough.....  this is great and all but not what I am looking for.  I am looking to get the date for the copied range from whatever is in the existing cell, in this case B7.  We cannot assume that it is the previous day, or the previous working day...  If a day is skipped, or someone is on vacation, or a number of days in a row are missed, those logics will not work.  It MUST be pulled from whatever the last day entered was, meaning the existing value of cell B7 is.  Nothing else.  

The latest code suggestion did not work...  possibly the sws.Range("M6:M???  I do not think suggested code was tested on the sample sheet that was provided.  Still trying to work with it..

So here is the issue... when TargetDate is used to autofilter:
Sheets("LogArchived").Select
Range("B7").Select
TargetDate = ActiveCell.Value  'brings back a format of: #3/14/2018 3:29:22 PM#

sws.Range("B6:L" & slr).AutoFilter field:=1, Criteria1:=TargetDate

Open in new window

TargetDate is:  #3/14/2018 3:29:22 PM#  which when filtered on returns nothing...  How do I adj this value so the autofilter works?

Is there another way to accomplish this, like using a For or Do While loop to capture the range to copy?  The range is not that big and will not be more the 20 or so rows, so I am not worried about it slowing the process down.
0
 
RWayneHAuthor Commented:
The first suggested code worked awesome, as long as the date was yesterday... but now that the Date in the provided sample file is no longer yesterday, that is failing too.
0
 
RWayneHAuthor Commented:
Ok I see what it is doing... it is converting the date to a number in column M, and filtering by that, instead of the value in Column B...  

I get it now, and yes it is working....   Thanks.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes, you got it right.
You're welcome! Glad it worked as desired.
0
 
RWayneHAuthor Commented:
Appreciate the help and patience with this issue..
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.