Copy / Paste data ranges to specific locations

[branston]

Hi

Not having any joy with this so hoping someone can help.


I am trying to copy a select set of data and paste to to another tab (sheet2). The data on sheet1 consists of 4 columns. Column1 is a set of candidate pictures, columns 2,3,4 are the candidate scores. I would like to automate this process rather than using VLOOKUP or formulaes etc. as my llist of sheet1 can grow.

Sheet 2 is almost set up like a template with pre-marked areas for which the data needs to be pasted into. The scores are pasted underneath pictures.

(For now I have pre-marked the areas on sheet 2 but long term if the data on sheet 1 increases, I don't know if it's possible to create new areas /enlarge template on sheet 2 'on the fly' ?)

I have attached my worksheet and made some progress. I have also shown some examples of what the data would look like on sheet2. ('jpg1', 'jpg2' etc. are just text examples of the pictures themselves as I cannot upload pics.)

Also, I don't want the pictures once pasted to increase the size of the (middle) cells they are being pasted into. They pictures are 3x5 cells in size so will take up 3x5 cells.

Could anybody have a look and offer any help?

[SamT]

Somewhere on sheet2 set up a 3x5 block of cells, Near that block, set up a 5x6 block

Record a Macro of you manually copying an image from sheet1 to the 3x5 block and sizing the image to fit.

Record another macro of you copying that block to the 5x6 block.

Post that workbook here. That will give us something to work with.

[snb]

Sub M_snb()
  For j = 2 To Sheet1.Shapes.Count
    Sheet1.Shapes(j).CopyPicture
    Sheet2.Paste Sheet2.Cells(Choose(j, 0, 7, 7, 7), Choose(j, 0, 11, 17, 36))
    With Sheet2.Shapes(Sheet2.Shapes.Count)
      .Placement = 3
      .Width = Sheet2.Cells(7, 11).Resize(, 3).Width
      .Height = Sheet2.Cells(12, 1).Resize(5).Height
    End With
  Next
End Sub

[branston]

Thanks snb - that's awesome.

Seems to hang up after two photos .. not sure what I'm missing? Also is data (ie. scores) added after the photos are copied across?

Attached with pics this time. Could you take a look pleeeeease?

Thanks again

[snb]

Please analyse the code.

Step through it with F8 in teh VBEditor.

As soon as you understand what it is doing you are able to adapt the code.
That should be your aim.

A hint in the right direction:

Sub M_snb()
  For j = 2 To Sheet1.Shapes.Count
    Sheet1.Shapes(j).CopyPicture
    Sheet2.Paste Sheet2.Cells(Choose(j, 0, 7, 7, 7, 14, 14, 14, 14), Choose(j, 0, 11, 17, 23, 8, 14, 20, 26))
    With Sheet2.Shapes(Sheet2.Shapes.Count)
      .Placement = 3
      .Width = Sheet2.Cells(7, 11).Resize(, 3).Width
      .Height = Sheet2.Cells(12, 1).Resize(5).Height
    End With
  Next
End Sub

You'd better use Excel formulae to add the scores.

[branston]

Thanks snb - that's great.

I've used the VBEditor to step through the code. It works fine... up to a point. I think I need more of the lines of code (like in question 1 below) if I have more data. I think I have a pretty much final version of a master template so this should be it for now. Once I have an even better idea of the code I think I can adjust it if my template was to ever change.


Could you help with me with the questions below?

1. if I have more rows/data then I would have to manually add more of these lines with the correct numbers for the rows/columns according to my template on sheet2?

 
Sheet2.Paste Sheet2.Cells(Choose(j, 0, 7, 7, 7, 14, 14, 14, 14), Choose(j, 0, 11, 17, 23, 8, 14, 20, 26))

2. I am developing this further and actually have 4 groups of data to insert into a venn diagram type template. Will this be a problem if using these lines of code ?

  For j = 2 To Sheet1.Shapes.Count
    Sheet1.Shapes(j).CopyPicture

I have attached my file again as this is turning out to be trickier than I thought.

(photos are dummies for test purposes)

Thanks for your patience and help with this - it's very much appreciated.

[p45cal]

Try the buttons in the attached.
There are 9 pictures in group 4 on sheet1 but only 8 spaces on sheet 2 for group 4 so I've shifted the errant picture on sheet1 (near cell I9) a bit to the left to take it out of the running.

[branston]

Thanks Pascal - this is a huge help! I will take a closer look in the morning.

Thanks again.

PS. Wanted to leave some rep but won't let me for some reason.