Apologies for not attaching the file, but for some reason the system won't upload it (It was created in Excel 2013 but in "compatability mode" as the end user has Excel 2007.
Objective is to find and copy all the data on the "Database" where the date in Col J matches the "Target" date in Col A of the "Date" sheet.
I have "Dim'd" the five variables involved, but I get a "RunTimeError 91 -Object Variable or With Variable Not set" message when the Code tries to set the ranges.
Code
Option Explicit
Sub DAYS()
Dim j As Long, l As Long, n As Long
Dim TDate As Range, PDate As Range, LPrs As Range, Now As Range, NPrc As Range
Dim ws As Worksheet
Dim Target
Sheet2.Activate
'Sets last row in sheet
j = Range("j65536").End(xlUp).Row
'If there is nothing below the heading it moves to next step.
'Otherwise it deletes everything from row 2 - Last row
If j >= 2 Then
Range("j2:P" & j).Select
Selection.Delete Shift:=xlUp
Range("j2").Select
End If
'Sets criteria to find (Single date set for this sample)
Sheet1.Activate
For n = 310 To 310
Target = Range("A" & n)
'Sets last row to search in Database
l = Range("J65536").End(xlUp).Row
Set TDate = ws.Rows(l).Find("Trading Date") 'Throwing "RunTimeError 91 -Object Variable or With Variable Not set"
Set PDate = ws.Rows(l).Find("Previous Day")
Set LPrs = ws.Rows(l).Find("Last Closing Price")
Set Now = ws.Rows(l).Find("Current Date")
Set NPrc = ws.Rows(l).Find("This Closing Price")
If (Not TDate Is Nothing) Then
With ws.Cells(l).CurrentRegion.Resize(, Application.Max(TDate.Column, PDate.Column, LPrs.Column, Now.Column, NPrc.Column))
.AutoFilter 1, Target
Union(.Columns(l), .Columns(TDate.Column), .Columns(PDate.Column), .Columns(LPrs.Column), .Columns(Now.Column), .Columns(NPrc.Column)).Copy
With Sheet2
.Range("J2").PasteSpecial
End With
End With
End If
Next
'Remove filters from DATABASE
Sheets("DATABASE").Activate
Application.CutCopyMode = False
Selection.AutoFilter
End Sub
Display More
Hope someone can see the flaw.
All responses accepted gratefully as ever
Ochimus