Title: Copy Image To All Workbooks In Directory
Tags: vba;excel
Question: I know how to open all workbooks in a directory, I am needing to open my source workbook, and copy an image Picture 100 from wbPicture.xlsx and for each wbdestination that is opened delete any shape that is below row 84 on each worksheet in the wbdestination.
I have googled and found that you can use this to copy an image from one workbook to another, but how to account for every worksheet, and how to remove the existing image (if their already is one)?
Sub CopyImage()
Dim imagewb As String
Dim openedwb As Workbook
Dim workbook As Workbook
Dim destbook As String
Dim totalbooks As Int
Dim bookname As String
Dim fulllist() As String
imagewb = "C:\Image.xlsx"
Set openedwb = Workbooks.Open(imagewb)
'Selecting image from template workbook
For Each shape in ActiveSheet.Shapes
If shape.Name = "Picture 100" Then
shape.Select
shape.Copy
End If
Next shape
Set WB = ActiveWorkbook
'Setting location of destination workbooks
destbook = "\\Hiya\ExcelFiles\"
totalbooks = 0
'Getting name of all .xlsx workbooks
bookname = Dir(destbook &amp; "*.xlsx")
'Creating array
totalbooks = totalbooks + 1
ReDim Preserve fullList(1 To totalbooks)
fullList(totalbooks) = bookname
bookname = Dir()
For int totalbooks = 1 To UBound(fullList)
Set openedwb = Workbooks.Open(destbook &amp; fullList(totalbooks))
'Selecting 1st sheet
Sheets(1).Select
'Pasting image from clipboard to workbook
With Sheets(1)
.Paste(.Range("A81"))
End With
'Saving workbook &amp; opening next
openedwb.Save
openedwb.Close False
End Sub

Here is the accepted answer: This will delete any image, regardless of name etc that is contained in the referenced range, in my example the referenced range is "A81:Z250"
For Each shape In ActiveSheet.Shapes
If Not Application.Intersect(shape.TopLeftCell, .Range("A81:Z250")) Is Nothing Then
If shape.Type = msoPicture Then
shape.Delete
End If
End If
Next shape
To reference each sheet contained in the workbook, pulled directly from MSDN KB
Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name
Next I
End Sub
Comment for this answer: thank you for that syntax, but when I try to iterate all sheets the syntax does not present an error, BUT it does not actually update all worksheets.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:07/10/15', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<vba><excel>', 'Title': 'Copy Image To All Workbooks In Directory', 'ViewCount': 54, 'id': 31346358, 'user_AboutMe': '', 'user_AccountId': 3535665.0, 'user_DisplayName': 'MasterOfStupidQuestions', 'user_Reputation': 935.0, 'user_Views': 287.0, 'user_id': 2954053.0}}