• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Dadbod

New Member
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 <<<
Code:
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)
 
Last edited by a moderator:

vletm

Excel Ninja
Dadbod
As a new member,
You have just read Forum Rules
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.
 
Dadbod, let me paraphrase back to you; study this and see whether I've understood you correctly:
Code:
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.
 
Top