How to rearrange a large group of pictures in an excel file via VBA?

I have a spreadsheet with a list of over 100 names in column A.  There is a picture next to each name.  I need to rearrange the names and pictures horizontally.  The names is easy enough, but is there a way to transpose the pictures? I did find some code that I posted below that can move the pictures, but I’m not sure if it can be modified to meet my needs.

Sub AlignMultipleShapes()
'PURPOSE: Align each shape in user's selection (first shape selected stays put)
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim Shp1 As Shape
Dim Shp2 As Shape
Dim x As Integer
Dim y As Integer

'Count How Many Shapes Are Selected
  x = Windows(1).Selection.ShapeRange.Count

'Loop Through each selected Shape (align with first selected)
  For y = 1 To x
    If Shp1 Is Nothing Then
      Set Shp1 = Windows(1).Selection.ShapeRange(y)
    Else
      Set Shp2 = Windows(1).Selection.ShapeRange(y)
        
        'Align Left
          Shp2.Left = Shp1.Left
              
        'Align Right
          Shp2.Left = Shp1.Left + (Shp1.Width - Shp2.Width)
  
        'Align Top
          Shp2.Top = Shp1.Top
  
        'Align Bottom
          Shp2.Top = Shp1.Top + (Shp1.Height - Shp2.Height)
  
        'Align Middle (Horizontal Center)
          Shp2.Top = Shp1.Top + ((Shp1.Height - Shp2.Height) / 2)
  
        'Align Center (Vertical Center)
          Shp2.Left = Shp1.Left + ((Shp1.Width - Shp2.Width) / 2)
          
      End If
  Next y

End Sub

Open in new window

kbay808Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you move the new first shape to the .shapes(<current top shape name>).topleftcell.address the rest I assume will follow along.
0
Mike in ITIT System AdministratorCommented:
Could you attach a sample file with maybe 10 pictures and names for us to test with?
0
kbay808Author Commented:
Here is an example file
Example-File.xlsx
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

cErasmusCommented:
Hi
Do you still need help with this?
If yes do you want the names in one row with the pictures below or do you want it to be in one row with the name and the picture next to it?
Elmo
1
kbay808Author Commented:
I'm looking to have the pictures in one row and the names listed below them.
0
cErasmusCommented:
Ok i'll have something for you later today
0
cErasmusCommented:
Hi

See if this works for you.

Elmo
PictureMover.xlsm
0
cErasmusCommented:
Hi
I did not include code to set the column widths or heights to correspond with the images or names but this can be done fairly easily. let me know if you need help with this.
Elmo
0
kbay808Author Commented:
I do need the column widths and height to correspond with the images so that the first row is the same height as the images and the columns be wide enough for the names to fit.  

The example file works fine, but when I use the code on my file, it deletes the first image.  Not a big deal though.  It’s easy enough to copy and paste.  Thank you for your help on this.
0
cErasmusCommented:
Hi

I will add the code for the column height and width for you sometime tomorrow. I find it strange that it deletes it image in your workbook. If possible i would like to see the workbook.

Elmo
0
cErasmusCommented:
this one should work for you
PictureMover.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
The code works perfect in the example, but it only works on 4 pictures.  In my spreadsheet I have 291 pictures.  I added more names and pictures to the file, so you can see what is happening.
PictureMover-v2.xlsm
0
cErasmusCommented:
Hi

This happens when images overlap into the cell above. Unfortunately since the images do not move with the cells i do not know of a way to make sure they are contained in a cell programmatically. Make sure images do not overlap into the cells above them and it will work

Elmo
0
kbay808Author Commented:
The issue that I was having was that in my working file I had headers and some names did not have a picture in column B causing the code not to work.  After I remedied those 2 issues the code worked perfect.  Thank you for all your help.  Great Job!!!
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.