Get Imahes From a range

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
104
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
I am getting Error 436 on line

Code:
wb.Sheets("Pictures").Shapes.Range(Array(PicName)).Copy
 
Upvote 0
Yes all images are named "Picture1" thru 6.

The error says

Run-time error '436' Object doesn't support this property or method
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top