Hello!
I am struggling with a problem I'm hoping can please help me solve. My data headers are on row 8. I need to filter column X to show dates before or equal to today and then select the first row of data and then all rows until the end.
I trimmed some out, but here is all I think you'll need to see of what I have so far.
I would really appreciate any assistance.
Thanks so much!
I am struggling with a problem I'm hoping can please help me solve. My data headers are on row 8. I need to filter column X to show dates before or equal to today and then select the first row of data and then all rows until the end.
I trimmed some out, but here is all I think you'll need to see of what I have so far.
VBA Code:
'Filter all date cells before or equal to today
Sheets("Quote Summary").Select
If Not Worksheets("Quote Summary").Range("A8").AutoFilter Then
Worksheets("Quote Summary").Range("A8").AutoFilter
End If
ActiveSheet.Range("X8").AutoFilter Field:=24, Criteria1:= _
"<=" & Now(), Operator:=xlAnd
'Copy from Quote Summary tab to Advantage Agreement Upload tab
Dim lr As Long, lr2 As Long, sh2 As Worksheet
Set WB1 = ActiveWorkbook
Set sh2 = Sheets("Advantage Agreement Upload")
With Sheets("Quote Summary")
lr = .Range("B" & Rows.count).End(3).Row
lr2 = sh2.Range("H" & Rows.count).End(3).Row + 1
sh2.Range("F" & lr2).Resize(lr - 8, 1).Formula = "=Opportunity!$P$2"
sh2.Range("F" & lr2).Resize(lr - 8, 1).Copy
sh2.Range("F" & lr2).Resize(lr - 8, 1).PasteSpecial Paste:=xlPasteValues
sh2.Range("H" & lr2).Resize(lr - 8, 1).Value = .Range("A9:A" & lr).Value
sh2.Range("L" & lr2).Resize(lr - 8, 1).Formula = "=IF(H25="""","""",VLOOKUP(K25,'Upload Data'!V:X,3,FALSE))"
sh2.Range("L" & lr2).Resize(lr - 8, 1).Copy
sh2.Range("L" & lr2).Resize(lr - 8, 1).PasteSpecial Paste:=xlPasteValues
I would really appreciate any assistance.
Thanks so much!