Get Imahes From a range

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
I am wanting to copy a range of images from one WB to another.

I am going to try to explain this as best as i can .

WB1 for example is the WB with the images and WB2 is where the images need to be copied to.

Inside of WB2 i have the Cells set so A1:A6 contain the Picture name and B1:B6 is where i want the pictures copied.

Example:
example_zps98207d58.png


Inside WB1 is the exact same setup but it included the images

Example:
example2_zps785df1d2.png


This is the Code i have came up with to get at least one image to be copied
Code:
Sub GetPics()
Dim PicRange As Range
Dim PicName As String
Set PicRange = Range("A1")
PicName = PicRange

Application.ScreenUpdating = False
Application.Workbooks.Open (ThisWorkbook.Path & _
     Application.PathSeparator & "Test2.xlsm")


    Sheets("Pictures").Select
    ActiveSheet.Shapes.Range(Array(PicName)).Select
    Selection.Copy
    Windows("Test1.xlsm").Activate
    Range("B1").Select
    ActiveSheet.Paste
    
    MsgBox "IMG Copied"

     
Workbooks("Test2.xlsm").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

This works fine however i am wanting the macro to get the value/text from each cell in A1:A6 and use that value as the picture name and then paste each picture in the correct position from B1:b6.

I am sorry if i didnt explain this good enough. This is just a test swatch when/if i get this figured out it will be for a much larger project.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try (untested):

Code:
Sub GetPics()
    Dim wb As Workbook
    Dim PicRange As Range
    Dim PicName As String
    Dim Cell As Range
    Set PicRange = Range("A1:A6")
    Application.ScreenUpdating = False
    Set wb = Application.Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Test2.xlsm")
    ThisWorkbook.Activate
    For Each Cell In PicRange
        PicName = Cell.Value
        wb.Sheets("Pictures").Shapes.Range(Array(PicName)).Copy
        Range("B" & Cell.Row).Select
        ActiveSheet.Paste
    Next Cell
    MsgBox "IMG Copied"
    wb.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub
 

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88

ADVERTISEMENT

Yes all images are named "Picture1" thru 6.

The error says

Run-time error '436' Object doesn't support this property or method
 

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88

ADVERTISEMENT

Wow that worked you sir are a genius!!
 

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
Andrew Poulsom

You have this line in the code
Code:
Range("B" & Cell.Row).Select

If I had a range of say B3:D5 how would I get the column to do like the Row?
</pre>
 

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
I mean within the range B3:D5

I know the code i mentioned works great for a single row however i cannot get it to work for each cell within a non-linear range.

So i would like to copy a single image to

Cells
B3,B4,B5,C3,C4,C5,D3,D4,D5
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top