VBA code to find cell contents within named range, copy to new row on another sheet

i am good with formulas but unfamiliar with VBA.

task: copy weekly results from Source sheet to a new row on Destination sheet, creating a 1-line journal entry of the weekly stats.
1. cell G13 on SOURCE sheet contains the text string i want to find within RESULTS, a named range elsewhere on SOURCE sheet.
2. find the cell in the range that contains this text, then use offset to copy the contents of the next column (same row), and paste to the first blank row after row 21, in column G on sheet "JOURNAL"

i have about 30 of these to do, all within the same named range.

i would like to assign to a button so when pressed, it copy/pastes all the values in one run.

thank you in advance

• if you have 30 of them and want to do them all at once, are all 30 located in G13:G43, or...
• what is the range of RESULTS?

Submit an example workbook which clearly demonstrates what you would like to occur.

thank you for the interest. this is the full scope of what i want to do:

if there is a tick in SOURCE column H, and column G matches column A, then copy cell in column B to latest week on DESTINATION sheet in appropriate column. otherwise if there is a tick in col H but columns do not match, stop and return an error. continue for remaining cells.

please lmk if i can explain further. thanks.

wow this is beautiful. error checking and everything. thank you very much!!

i will study it and try to learn something from it.
cheers

i am getting an Excel crash as i adapt this for more data records. i think it happens when there are not sufficient columns on the Destination sheet. can you include this factor in the error checking ?

i think the crash was related to a MsxBox i tried to add at the end, alerting that the Copy was successful.
i had: MsgBox "Records copied." just before the end line of Application.ScreenUpdating = True

also, how can i modify this to avoid looking in rows 1-7 (and start looking in row 8) ? thanks.

The code does not increase the columns on Dest sheet when compiling records and instead searches for a match in the existing columns, hence, I doubt the crash would happen there. What line was highlighted during crash. (Note: It will crash if you have no "x" or "X" in any row in column H but I don't see why you would run the code without at least one "x" so I didn't add the extra code)

Amended code for starting at row 8 and added Msgbox

Please Login or Register  to view this content.

not sure. haven't been able to repeat the crash since i removed the MsgBox line i added. Excel exits immediately without time to see any kind of line highlighting


i am widening the scope of this a bit, need to move some columns around etc. there are 120+ records for each week and i'm not sure which i can eliminate. i wish i understood the code better so i could adapt it myself. but this is rather advanced for me. if you feel like explaining what the lines do, that would be great but no pressure.
i will report back after i play with it a bit more. thanks again.

I can explain the code if you want, but I don't know if my explanation skills are good enough to help you understand. Let me know if you still want explanation.

If you submit your actual workbook, after you get done rearranging things, we may be able to get you better and more encompassing code.