copy and paste if column contains a date

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
Hi Guys,

Is it possible to copy data from one tab to another using vba based on a column containing a date?

What I want to achieve is – if column E in tab ‘Total Outstanding’ contains a date, then copy all these entries (including all data from column A to J) to tab’ Challenges’

Many thanks
Craig
 

Attachments

  • Challenges OS age profile.xlsx
    320.5 KB · Views: 10

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Public Sub CopyData()Dim rng As Range
Dim lastrow As Long


    With Worksheets("Total Outstanding")
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("E1").AutoFilter
        Set rng = .Range("A1:J1").Resize(lastrow)
        rng.AutoFilter Field:=5, Criteria1:="="
        On Error Resume Next
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        If Not rng Is Nothing Then
        
            rng.Copy Worksheets("Challenges").Range("A1")
            Worksheets("Challenges").Columns("A:J").AutoFit
        End If
        
        .Range("E1").AutoFilter
    End With
End Sub
 

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
thanks very much, however, its bringing up an error when I run it via the module?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
The forum put that Dim rng As Range line on the first line, it should be a separate line.
 
Top