January 21, 2019

Insert Picture in Multiple Worksheets Automatically

How to insert a picture in multiple worksheets automatically with VBA.
A user has multiple sheets in a workbook as tax invoice, original for recipient, duplicate for transport, triplicate for Supplier, an extra copy for records, an estimate sheet, proforma invoice sheet and also a worksheet called Info. When he places a logo in the Info sheet in a specific cell it should be replaced with the existing logo in all sheets. Watch the video below to see how an automatic solution to the problem has been achieved using VBA:

Watch this video on YouTube.

Here’s the complete VBA code to insert a logo automatically in many worksheets simultaneously:

Module1

Sub AddPic()

Dim myPicture As Variant
Dim p As Object

myPicture = Application.GetOpenFilename(“Pictures(*.gif;*.jpg;*.bmp;*.tif),*.gif;*.jpg;*.bmp;*.tif”, , “Select Picture to Import”)

If myPicture = False Then Exit Sub

For Each Sheet In Sheets
Sheet.Activate
On Error Resume Next
Call DeleteLogos
Range(“A1”).Select
Set p = ActiveSheet.Pictures.Insert(myPicture)
Next
End Sub

Module 2

Sub DeleteLogos()
Dim myObj
Dim Pictur
Set myObj = ActiveSheet.DrawingObjects
For Each Pictur In myObj
If Left(Pictur.Name, 7) = “Picture” Then
Pictur.Select
Pictur.Delete
End If
Next
End Sub

Download a sample file for practice:

Further Reading:

Place Picture in Excel Worksheet Cell Using VBA

How to Display Pictures on Excel Worksheet Using VBA

Place Picture Excel Worksheet Range into User Form with VBA

How to populate image control in Excel user form with data from worksheet using VBA

How to insert a watermark in an Excel worksheet

One thought on “Insert Picture in Multiple Worksheets Automatically

  1. Good eve Sir, it’s Major Dinesh from Indian Army, I made a customised userform by watching your videos. Thx a lot for ur guidance. I facing a problem in running Update command. As once I test run from VBA window it’s working fine, not finding data once userform displayed thru Button in worksheet, request if u can help me on this issue pl.
    Thx in anticipation.

Comments are closed.