Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

VBA Code

Hi,

I have attached a file which is a snapshot of a large file i receive each day, i want to take data from this file (inc headings) only if it is yesterdays date and put it in another ss.

So i know i would need some code that would sort the data by yesterdays date and then copy/ paste it into my other sheet (xyz.xls)

Can anyone suggest anthing?

Thanks
Seamus


ST.xls
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

There are 4 date fields.

Which one has to be tested against 'yesterday's date'
Avatar of Seamus2626

ASKER

Sory Peter, Column M

Thanks
Seamus
Seamus:

1) Do you want the code to run from ST.xls?
2) Where is the file xyz.xls?

Sid
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry guys, just got back to the desk, ive tested yours Sid and its blocking me on line

 delRange.EntireRow.Delete

Its saying "Object Variable or with Variable not set"

Il have have a look at yours nown jigneshthar

Thanks
Seamus
Try this

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Wb1Sh1 As Worksheet, Wb2Sh2 As Worksheet
    Dim lastRow As Long, i As Long
    Dim delRange As Range

    Set wb1 = ActiveWorkbook
    Set Wb1Sh1 = wb1.Sheets("Outstanding Items Report-1")
    
    Set wb2 = Workbooks.Open("C:\xyz.xls")
    
    Wb1Sh1.Copy Before:=wb2.Sheets(1)
    
    Set Wb2Sh2 = wb2.Sheets("Outstanding Items Report-1")
    
    lastRow = Wb2Sh2.Range("M" & Rows.Count).End(xlUp).Row
    
    wb2.Activate
    
    For i = lastRow To 2 Step -1
        If DateDiff("D", Date, Range("M" & i)) = 1 Then
            If delRange Is Nothing Then
                Set delRange = Range("M" & i)
            Else
                Set delRange = Union(delRange, Range("M" & i))
            End If
        End If
    Next
    
    delRange.EntireRow.Delete
    
    wb2.Close savechange:=True
    
    Set Wb2Sh2 = Nothing
    Set wb2 = Nothing
End Sub

Open in new window


Sid
Same error Sid.

I checked your sub jigneshthar and the autofilter is not working, its deleting all entries including yesterday!

Thanks
Seamus
One last try...

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Wb1Sh1 As Worksheet, Wb2Sh2 As Worksheet
    Dim lastRow As Long, i As Long
    Dim delRange As Range

    Set wb1 = ActiveWorkbook
    Set Wb1Sh1 = wb1.Sheets("Outstanding Items Report-1")
    
    Set wb2 = Workbooks.Open("C:\xyz.xls")
    
    Wb1Sh1.Copy Before:=wb2.Sheets(1)
    
    Set Wb2Sh2 = wb2.Sheets("Outstanding Items Report-1")
    
    lastRow = Wb2Sh2.Range("M" & Rows.Count).End(xlUp).Row
    
    wb2.Activate
    
    For i = lastRow To 2 Step -1
        If DateDiff("D", Date, Wb2Sh2.Range("M" & i)) = 1 Then
            If delRange Is Nothing Then
                Set delRange = Wb2Sh2.Range("M" & i)
            Else
                Set delRange = Union(delRange, Wb2Sh2.Range("M" & i))
            End If
        End If
    Next
    
    delRange.EntireRow.Delete
    
    wb2.Close savechange:=True
    
    Set Wb2Sh2 = Nothing
    Set wb2 = Nothing
End Sub

Open in new window


Sid
Thanks guys!