Find column and then paste vba excel

Question

I need to save some data by just clicking a button.

It should be easy but I dont know the syntax for vba.

Range("P2:P47").Copy

Sheets("All Data").Select
Range("J2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

I have 2 problem with this code,

1 there is "a lot of" selections that I do not know how to get rid of(I know you should try not to use selections).

2 My real problem is this: cell J2 in the code should not be a determined cell. I would like to have a "lookup value" and search for that in an array, and then using the mathing cell to paste my values.

So in my workbook cell A1 = "Aug 19" and in row 2 all the months are listed. So cell A2 = "Jan 19", B2 = "Feb 19" etc.

I would like to select cell H2 and then pasting by matching my lookup value "Aug 19" to H2 contains "Aug 19"

So in excel formulas I would basically just write:

=HLOOKUP(A1;'All Data'!$B$2:$AL$2;1;FALSE)

Solved problem 1

Sub savedata()

    Range("P2:P47").Copy

    Worksheets("All Data").Range("J2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

EDIT on problem 2:

Sub savedata()

    Range("P2:P47").Copy

    ActiveSheet.Range("C3:K3").Find(What:="Aug 19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

This work as I need it to! Still som questions tho, I couldn't figure out how to reference cell A1 instead of "Aug 19".

So your gross question is "help me rewrite this code?" Have you looked into `.find()` in VBA to support finding the cell/range that contains your desired value? There are many posts on these aspects of your desired final output, where you would need to put those together. — Cyril, Oct 15 '19 at 15:22
References... [Avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [Using `.find()`](https://stackoverflow.com/questions/30161124/vba-find-and-adding-a-value/30162390) — Cyril, Oct 15 '19 at 15:24
Well, not really. I basically only need: Find value A in the array and then select the cell where the match was. I do not know the syntax for this. that is what I need. — Nick, Oct 15 '19 at 15:24
Take a look at those links and attempt to apply those bits of code to your desired outcome. Once you have tried that, and if you still have questions with your newly-developed script, please [edit your post](https://stackoverflow.com/posts/58397965/edit) to include the code, the error/issue, and the line of the error/issue. — Cyril, Oct 15 '19 at 15:29

score 0 · Accepted Answer · answered Oct 16 '19 at 06:55

I solved it. I ended up with more selections that I would like but I tried to minimize it but I got a lot of error codes that way. see code below:

Sub savedata()

    G = Range("A1").Value

    Range("P2:P47").Copy

    Worksheets("All Data").Select
    Range("C2:AL2").Find(What:=G).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Worksheets("Add Data").Select
    Application.CutCopyMode = False
    Range("C4:K34").ClearContents

End Sub

I would like to do like this bu this does not work: Sub savedata()

    G = Range("A1").Value

    Range("P2:P47").Copy

    Worksheets("All Data").Range("C2:AL2").Find(What:=G).PasteSpecial 
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Worksheets("Add Data").Select
    Application.CutCopyMode = False
    Range("C4:K34").ClearContents

End Sub

Find column and then paste vba excel

1 Answers1