Pasting copied data to a cell based on the value of a cell in another worksheet

[Dadbod]

Hello beautiful people

I am currently teaching myself VBA coding in Excel and have answered most of my questions with Google searches, it has been an interesting, fun but also draining project so far. That being said I am having problems with this last part of the database I am setting up.

SCENARIO
I have 2 sheets (Sheet1 Name = HOTO / Sheet2 Name = Latest) and what I want to do is copy the data entered into sheets("HOTO").range("A2:F9") and then search column "a" on sheets("latest") and find the value that is in "b2" on Sheets("HOTO") and then select the cell one column across and one cell up and paste the data. eg: it finds the value Monday in cell "B2" on sheets("HOTO"), it then searches for that value in column "a" on sheets("latest") and finds it on row"19". Having found the value in then pastes the copied data in cells "B18:G25".

Once the data is pasted it then returns to Sheets("HOTO") and clears the data ready for the next entry.

This is what I have so far:

>>> use code - tags <<<

Private sub HOTO_click()

    sheets("hoto").select
    sheets("hoto").range("a2:f9").copy

'this is the code I am missing'

    selection.pastespecial paste:=pastevalues
    sheets("HOTO").select
    activesheet.range("a2").clearcontents
    activesheet.range("b2").clearcontents
    activesheet.range("c2:c9").clearcontents (merged cells)
    activesheet.range("d2:d9").clearcontents (merged cells)
    activesheet.range("e2:e9").clearcontents (merged cells)
    activesheet.range("f2:f9").clearcontents (separate cells)

End Sub

As I said I have been teaching myself through research but I just can't find this last part.
Any help would be greatly appreciated.

Thanks in advance
Mathew Kelly (DadBod)

 

[vletm]

Dadbod
As a new member,
You have just read Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Please, reread those.
Especially part of How to get the Best Results at Chandoo.org
Isn't there eg text like: For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.

 

[BobBridges]

Dadbod, let me paraphrase back to you; study this and see whether I've understood you correctly:

owsH = Worksheets("HOTO")
owsL = Worksheets("latest")
orgH = Range(owsH.Cells(2,1), owsH.Cells(9, 6)) 'set range to HOTO!A2:F9)
vs = owsH.Cells(2, 2).Value 'get the value in B2
' search for vs in owsL.Columns(1).  Pretend we know how to do that; we'll figure it out.
' Set ocL to the cell where that value is found (and what do you want to do when it's not found?).
set ocX = ocL.Offset(-1, 1) 'start the target range one row up and one column right
Set orgL = Range(ocX, ocX.Offset(7, 5)) 'target range
orgL.Value = orgH.Value

I'm not saying this program is all correct, but is it roughly what you want to accomplish? If so, you can refine the details—or we can, if you don't see your way clear yet.