Results 1 to 4 of 4

Thread: copy and paste if column contains a date

  1. #1

    copy and paste if column contains a date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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

  3. #3
    thanks very much, however, its bringing up an error when I run it via the module?

  4. #4
    The forum put that Dim rng As Range line on the first line, it should be a separate line.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •