Copy paste region to another exact location(s)

You can use Vlookup.
You could keep the picture name beneath the picture. Just like you have it.

Formula:

Please Login or Register  to view this content.

Then copy range K12:M12 and paste formulas into q12,w12,h19,n19.....ect

Hi dave

Thanks for the idea. Ideally I wanted it all automated via a macro as the list on sheet 1 can grow and it's just time consuming have to copy paste all the time. BTW the .jp1, jpg2 etc. on sheet2 was just typed there to show that that's where the picture sits. There is no text there in actuality … just a picture.

I set up the template on sheet 2 and think I could use an array function with the .offset feature? I have seen it done before but not 100% how to code it. Still new and learning.

You attached sample workbook, click on the pictures to see the picture name.

The code will loop through the pics and search sheet 1 for the picture name, then place the data in the correct cells.

Click the button.

Please Login or Register  to view this content.

I will have to get back to this, I have to go out.

You can loop through the cells and identify specific cells with specific borders.

I have just worked out this code, it will loop through the cells and color the ranges that will have the pics.

Test it out! Now we have a way to find the picture ranges.

Please Login or Register  to view this content.

There is some prep work to do 1st

1- name the group ranges.( do this manually)

2-loop through the pictures and name them
3-Name the ranges in Sheet(2) so we know where to paste the pictures.
4-loop through the pictures and compare named ranges to see if they match. (If there are more picture names than range names you will get a message and will need to fix before copy and pasting the pics.)
5-copy and paste pictures to the named ranges and get the info for each picture.

As of right now, you have 9 pictures for group 4 in sheet(1), but only have 8 ranges in group 4 sheet(2),
so the message-box will pop up telling you, you have to fix this then run the code again.

-
Code to name the pictures.

Please Login or Register  to view this content.

-

Code to create the named ranges for the pics to be pasted in.

Please Login or Register  to view this content.

Name Group1.jpg
Name the pictures.jpg
NamePicRanges.jpg

It looks like you haven't completed the 1st part of my instructions. Naming the group ranges., you have to do this manually.

Name them according to the sample I gave you, the code will be looking for those group names.

Go through the sample workbook and look at the named ranges, there are named ranges for the groups in sheets 1 & 2

Note: The top of the pictures cannot over lap into another cell

Ok thanks dave.. think I've done that properly now however can you tell me if I have the candidate name and number to the left of (each) photo on each group do I need to adjust this line of code below (e.g. 1. - John Smith - PHOTO - Score 1 - Score 2 - Score 3) when looping through to name the pics?

Please Login or Register  to view this content.

as I am getting a 'no cells were found' error when trying to loop through and name the pics

thanks

The code was designed for the sample you provided, you had changed the sample, now the code needs to be edited.

Try it now.
There are still more pictures in group 3 sheet 1 compared to group3 sheet2

Are you using the same codes in the previous attachment?

I can't tell, you attached a word doc with pictures?

I don't know, you haven't supplied the wb that causes that error.

It just removes the named ranges the pictures are going to go into, then the code names the ranges again, this was to make sure there are no mismatched range names to picture names.

Dave - quick question. What is the best way to do the following:

Once the pics are copied across to sheet2, I need to vlookup some data based on the pic in cell x (for that candidate). Is it possible to do a vlookup using the pic itself as the reference point?


I'm thinking 2 options:

1.I could place some data behind the pic in sheet1 (ie. the 4 columned grouped lists) but when the pics are copied across, the data in the cell (behind the pic) is not copied onto sheet2. If it was, then I could just use that (relative) data for e.g. an admission number, to do the vlookup to get hold of other data.
2.Use the existing pic names themselves e.g. Group1Pic1 and use for. e.g

Please Login or Register  to view this content.

function to get the pic filename. Then perhaps do a

Please Login or Register  to view this content.

to match the pic filename with the admission number (ie. without .jpg on the filename) to get data from a different tab.

Hope the above makes sense and would appreciate any guidance here.

Thanks

Make the named ranges in sheet 1 include all the columns, then your copy code can determine what group to include in the vlookup formula.

Please Login or Register  to view this content.

Thanks Dave!

I will mark the thread as solved although I have just realised that when resetting the pictures on sheet2, all the data disappears (for e.g. the age, salary, job title etc. etc. of a candidate which I now have from the VLOOKUP). This means I have to manually copy/paste the vlookup formulae for EVERY candidate again. Is there a way around this? Could the reset button only delete the 15 (3x5) small cells for each candiate that sit within the 5x6 range for each candidate (because the other data goes around the pic.)

If its any help all of the candidate personal information sits in a 'main data' worksheet.

Thanks once again for everything.

The code makes the formula, why would you need to type it back in?

This is where the pic range is set

Set PicRng = Range(Cells(rw, cl), Cells(rw + 5, cl + 2))

You can change the 5 to a 4

Sorry Dave - I have probably haven't explained myself well enough.

If you look at the attached sheet, hopefully you'll see what I mean. There would be VLOOKUP formulas going either side of the photo, the attached shows a few examples (dummy data) only though.

Not sure when copying the pics across to sheet2, the code-loop can also extract/copy/paste other data from the 'main data' tab at the same time? This way if all the data is deleted on reset it doesn't matter as easily retrieved when running 'get pics' routine?

As ever, thanks for your help.