Search a copied value MACRO

Question

I have two sheets:

Database
Macro sheet: It has a row with dates that will be the headings of a table after the macro.

Objective: In the macro sheet take the value of the first date and look for its position in the database sheet. Then, in the database sheet, copy the entire column corresponding to the previously copied date.

I understand that the code should look something like this:

    Sheets("Macro").Select
    Range("K3").Select
    Selection.Copy
    Sheets("Database").Select
    Cells.Find(What:=Selection.PasteSpecial xlValues, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Macro").Select
    ActiveSheet.Paste

This code does not work, because the search part is not done well, I will appreciate some correction

score 1 · Accepted Answer · answered Dec 13 '18 at 12:56

1

Something along these lines.

Read this to learn the advantages of not using Select or Activate.

When using Find, always check first that your search term is found to avoid an error. For example, you cannot activate a cell that does not exist.

Sub x()

Dim r As Range

With Sheets("Database")
    Set r = .Cells.Find(What:=Sheets("Macro").Range("K3").Value, lookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    If Not r Is Nothing Then
        Range(r, r.End(xlDown)).Copy Sheets("Macro").Range("A1")
    End If
End With

End Sub

answered Dec 13 '18 at 12:56

SJR

22,986
6
18
26

It works quite well, but I have a problem, I do not know why in one of the rows of the pasted column a text appears: Selection.PasteSpecial Paste: = xlPasteValues – cavezonk Dec 13 '18 at 13:18
please tell me if you know why a cell takes that value – cavezonk Dec 13 '18 at 13:28
I don't know. The code only copies and pastes what you have on your worksheet. Might be better to use `End(xlup` in case you have a blank column. – SJR Dec 13 '18 at 13:38
1

I do not know why I really had a row in the database with Selection.PasteSpecial Paste: = xlPasteValues ... I really do not know how it happened Hahaha. Your code works perfect, thank you very much :) – cavezonk Dec 13 '18 at 13:46

score 1 · Answer 2 · answered Dec 13 '18 at 13:00

Loop through he header dates in the Macro worksheet. If any can be found in the header row of the Database worksheet, copy that column to the Macro worksheet under the header.

sub getDateData()

    dim h as long, wsdb as worksheet, m as variant, arr as variant

    set wsdb = worksheets("database")

    with worksheets("macro")

        for h=1 to .cells(1, .columns.count).end(xltoleft).column

            m = application.match(.cells(1, h).value2, wsdb.rows(1), 0)

            if not iserror(m) then
                arr = wsdb.range(wsdb.cells(2, m), wsdb.cells(rows.count, m).end(xlup)).value
                .cells(2, h).resize(ubound(arr, 1), ubound(arr, 2)) = arr
            end if

        next h
    end with

end sub

Search a copied value MACRO

2 Answers2