mardi 20 mars 2018

VBA - Next, If, and copying only data which matches an input

It's been a decade since I've written VBA and trying to reach out to see what I broke. I wrote a macro which copies data from one sheet to another, 1 column at a time for 4 different columns, and pastes it in the next free cell. This formula worked but I would like to adjust it to only copy certain data. Below is an example, I am trying to only copy A if the date value in E is equal to the input date value you enter when the macro starts. I am having most trouble balancing the If/Then with the For/Next. Everytime I place an End If or Next I receive errors. Any assistance much appreciated.

Dim DateValue As Variant
DateValue = InputBox("Enter the date to copy")

'copy and paste column A to column A if E = input date
For Each Cell In Worksheets("Enrichment Report").Range("E:E")
    If Cell.Value = DateValue Then
        Sheets("Enrichment Report").Select
        iMaxRow = 100
        For iCol = 1 To 1
            For iRow = 2 To iMaxRow
                With Worksheets ("Enrichment Report").Cells(iRow, iCol)
                    If .Value = "" Then
                        'empty row, do nothing
                    Else
                        .Copy
                        Sheets("Intake Form").Select
                        Range (A" & Rows.Count).End(xlUp).Offset(1).Select
                        Activesheet.Paste
                    End If
                End With
            Next
        End If
    Next iRow
Next iCol

Aucun commentaire:

Enregistrer un commentaire