copy selected cells in a row/s to another worksheet based on date-VBA

[dibyendu]

Hello,

I need to copy data from worksheet "Records" to worksheet "Reviews" based on date (msg. box= which date?).
In worksheet "Records" date is in column "BV". I need to copy column "C", "D","BU", "BV","BW", "BX" from worksheet "Records" when particular date of interest matches and paste it to column "A", "B", "C", "D", "E", "F" of worksheet "Reviews"(The first row should remain blank for adding "Headings"). Evey time when VBA runs the previous data in worksheet "Reviews" should be clear(delete). The VBA code needs to search from 10,000 rows from worksheet "Records". I am new to VBA and need your support. Thanks for your support.

 

[Fluff]

Unfortunately, sending me a sample isn't that helpful, as the problem lies with the date format.
Your file simply shows my date setting and works fine.
However I have made a few tweaks to the code.

Sub FindNext_Copy_Data()

    Dim RecSht As Worksheet
    Dim RevSht As Worksheet
    Dim Mydate As Date
    
    Application.ScreenUpdating = False

    Set RecSht = Sheets("Records")
    Set RevSht = Sheets("Reviews")
    
    RecSht.Unprotect ("ds123456")
    RevSht.Unprotect ("ds12345678")
    RevSht.Range("A2:H" & Rows.Count).ClearContents
    Mydate = InputBox("Which Date ? MM/DD/YYYY")

    RecSht.Columns("BV").AutoFilter
    With RecSht.Range("BV:BV")
        .AutoFilter Field:=1, Criteria1:=Format(Mydate, "MM/DD/YYYY")
        RecSht.Range("C5:D10000").SpecialCells(xlVisible).Copy RevSht.Range("A2")
        RecSht.Range("BU5:BX10000").SpecialCells(xlVisible).Copy RevSht.Range("C2")
    End With
    RecSht.Range("B1").AutoFilter

    RevSht.Activate
    Range("A2", Range("F" & Rows.Count).End(xlUp)).Sort _
    Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    RevSht.Protect ("ds12345678")
    RecSht.Protect ("ds123456")


End Sub

Can you confirm that your dates are in col BV?

 

[dibyendu]

I may be wrong in saying that in VBA there is no paste option in (Reviews) for the selection(copy) in "Records" sheet

 

[Fluff]

Firstly, can you please answer this?

Can you confirm that your dates are in col BV?

Secondly, the part in red is the "Paste", what it is doing is copying the data direct to the review sheet, bypassing the clipboard

        RecSht.Range("C5:D10000").SpecialCells(xlVisible).Copy [COLOR=#ff0000]RevSht.Range("A2")[/COLOR]
        RecSht.Range("BU5:BX10000").SpecialCells(xlVisible).Copy [COLOR=#ff0000]RevSht.Range("C2")[/COLOR]

 

[dibyendu]

yes, I confirm that your dates are in col BV.

 

[Fluff]

OK run this

Sub FindNext_Copy_Data()

    Dim RecSht As Worksheet
    Dim RevSht As Worksheet
    Dim Mydate As Date
    

    Set RecSht = Sheets("Records")
    Set RevSht = Sheets("Reviews")
    
    RecSht.Unprotect ("ds123456")
    RevSht.Unprotect ("ds12345678")
    RevSht.Range("A2:H" & Rows.Count).ClearContents
    Mydate = InputBox("Which Date ? MM/DD/YYYY")

    RecSht.Columns("BV").AutoFilter
    With RecSht.Range("BV:BV")
        .AutoFilter field:=1, Criteria1:=Format(Mydate, "MM/DD/YYYY")
        RecSht.Select
        RecSht.Range("A1").Select
        MsgBox "Is anything visible"
        RecSht.Range("C5:D10000").SpecialCells(xlVisible).Copy RevSht.Range("A2")
        RecSht.Range("BU5:BX10000").SpecialCells(xlVisible).Copy RevSht.Range("C2")
    End With
    RecSht.Range("B1").AutoFilter

    RevSht.Activate
    Range("A2", Range("F" & Rows.Count).End(xlUp)).Sort _
    Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
    RevSht.Protect ("ds12345678")
    RecSht.Protect ("ds123456")


End Sub

You should see a message box appear & the records sheet should be visible. Can you see any data in the record sheet, or has it all been filtered out?

 

[dibyendu]

Thanks for your help...