Same Picture on multiple Sheets

E

ElvisHess

Board Regular
Joined
May 4, 2006
Messages
150
I currently have a spreadsheet that requires the same picture on each of the 7 worksheets within the spreadsheet. I have to go into each page and insert it seperatly now. Does anyone know of a way I can insert it once and have it automatically show up on the other 6 pages.

Thanks in Advance
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
I don't know if this will cork or not, but you could try selecting all the sheets at the bottom that you want to insert it into and try that maybe? Or just copy and paste the picture after you put it in the first sheet to the rest of the sheets.

Sorry I don't work with pictures in excel much, so not sure if this works or not.
 
Upvote 0
One way, though the resolution won't be quite perfect on the copies, is to have the picture be located on one page, and have the pictures on the other 6 pages be linked to *the cell(s) containing* the picture.

You can try it out as follows:

Put the picture on one page, say Sheet1.
Copy the picture.
Paste it to another page.
Click on the copied picture, then click into the formula bar and type =Sheet1!$A$1

...Your picture copy is now linked to cell A1 on Sheet1. If, before doing the copying and linking, you make sure that your picture is sitting exactly in the cell(s) on Sheet1 - in this case cell A1 - then it doesn't look too bad. Especially if every time you replace the picture, it is the same size and you can put it into the cell(s) without changing their dimensions. Depending on how you finesse it, the linked pic can be more or less distorted.

Of course, you could probably mess around with your header or footer in order to get the picture to be where you want it... Then you could do page setup for all sheets at the same time, and change out the picture that-a-way.

Schielrn, I tried your suggestion and I don't think you can work with objects while multiple sheets are selected...
 
Last edited:
Upvote 0
I tried that and got the following message

The text you entered is not a valid reference or defined name.

Here is the code I'm using to put the picture on the spreadsheet

Private Sub btnPic1_Click()
ActiveSheet.Unprotect Password:="elvis1"
Dim myPicture As Variant
Dim p As Object
Dim Factor As Single
Range("BQ6").Select
myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.cgm; *.jpg; *.bmp; *.tif),*.gif; *.cgm; *.jpg; *.bmp; *.tif", _
, "Select Picture to Import")
If myPicture = False Then Exit Sub
Set p = ActiveSheet.Pictures.Insert(myPicture)
'Width and Height are in points (1/72 inch)
p.ShapeRange.LockAspectRatio = msoTrue
Hfactor = 5 / (p.Height / 72)
Wfactor = 5.69 / (p.Width / 72)
If Hfactor < Wfactor Then
Factor = Hfactor
Else
Factor = Wfactor
End If
p.Width = p.Width * Factor
p.Height = p.Height * Factor
ActiveSheet.Protect Password:="elvis1"
End Sub
 
Upvote 0
ElvisHess said:
I tried that and got the following message

The text you entered is not a valid reference or defined name.
Click to expand...

OK, then instead of typing in =Sheet1!A1, type in the = sign, and use your mouse to select the cells containing the picture.
 
Upvote 0
I have a logo that needs to be on any number of sheets in my purchase order workbook. I copy the range of rows (header) on the first sheet, then select the remaining sheets & insert above row one (it's a header). The logo along with whatever information is in the rows I select is copied to the remaining sheets.
 
Upvote 0
If you want to use that code to add the pix - and if you want it to be in the same spot (BQ6) in every sheet - and if all the sheets have the same password:

Code: 
Sub AddPic()

Dim myPicture As Variant
Dim p As Object
Dim Factor As Single
myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.cgm; *.jpg; *.bmp; *.tif),*.gif; *.cgm; *.jpg; *.bmp; *.tif", _
, "Select Picture to Import")
If myPicture = False Then Exit Sub
For Each page In Sheets
    page.Activate
    Range("BQ6").Select
    ActiveSheet.Unprotect Password:="elvis1"
    Set p = ActiveSheet.Pictures.Insert(myPicture)
    'Width and Height are in points (1/72 inch)
    p.ShapeRange.LockAspectRatio = msoTrue
    Hfactor = 5 / (p.Height / 72)
    Wfactor = 5.69 / (p.Width / 72)
    If Hfactor < Wfactor Then
        Factor = Hfactor
    Else
        Factor = Wfactor
    End If
    p.Width = p.Width * Factor
    p.Height = p.Height * Factor
    ActiveSheet.Protect Password:="elvis1"
Next
End Sub

...but if you are using this to replace the picture periodically, keep in mind that you will be adding the new picture on top of the old one. So the old picture is still there taking up memory.
 
Upvote 0
I use the following code to delete the pictures if it happens to be updated. I have a button for each of the macro's on each of the 7 sheets in the spreadsheet. Just trying to streamline my process to accomodate low end users.

Private Sub btndel1_Click()
ActiveSheet.Unprotect Password:="elvis1"
On Error Resume Next
Dim ShapeObject As Shape

For Each ShapeObject In ActiveSheet.Shapes

' Uncomment the following line to get a prompt of every shape object type.
' Write down all the Types it shows you, and use trial-and-error to figure out what type any given picture is.
' KNOWN TYPES: Form Buttons are Type 12, Drop Down Boxes are Type 8, JPegs and GIfs are Type 13
' The Message Box will pop-up for every shape it encounters, which may be dozens, so keep clicking "OK" until it finishes the loop

'MsgBox ShapeObject.Type

If ShapeObject.Type = 13 Or ShapeObject.Type = 7 Then
Call ShapeObject.Delete
End If
Next
ActiveSheet.Protect Password:="elvis1"
End Sub
 
Upvote 0
OK, then you could add that in like this:

Code: 
Sub AddPic()
 
Dim myPicture As Variant
Dim p As Object
Dim Factor As Single
myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.cgm; *.jpg; *.bmp; *.tif),*.gif; *.cgm; *.jpg; *.bmp; *.tif", _
, "Select Picture to Import")
If myPicture = False Then Exit Sub
For Each page In Sheets
    page.Activate
    ActiveSheet.Unprotect Password:="elvis1"
'DELETE THE OLD PICTURES
On Error Resume Next
Dim ShapeObject As Shape
 
For Each ShapeObject In ActiveSheet.Shapes
 
' Uncomment the following line to get a prompt of every shape object type.
' Write down all the Types it shows you, and use trial-and-error to figure out what type any given picture is.
' KNOWN TYPES: Form Buttons are Type 12, Drop Down Boxes are Type 8, JPegs and GIfs are Type 13
' The Message Box will pop-up for every shape it encounters, which may be dozens, so keep clicking "OK" until it finishes the loop
 
'MsgBox ShapeObject.Type
 
If ShapeObject.Type = 13 Or ShapeObject.Type = 7 Then
Call ShapeObject.Delete
End If
Next
 
'FINISHED DELETING OLD PICTURES
    Range("BQ6").Select
    Set p = ActiveSheet.Pictures.Insert(myPicture)
    'Width and Height are in points (1/72 inch)
    p.ShapeRange.LockAspectRatio = msoTrue
    Hfactor = 5 / (p.Height / 72)
    Wfactor = 5.69 / (p.Width / 72)
    If Hfactor < Wfactor Then
        Factor = Hfactor
    Else
        Factor = Wfactor
    End If
    p.Width = p.Width * Factor
    p.Height = p.Height * Factor
    ActiveSheet.Protect Password:="elvis1"
Next
End Sub

...this should prompt for the user to choose a picture, then will go through to every sheet, delete all of its pix and add the new one on cell BQ6.
 
Upvote 0
This works good, With the exception if the cell BQ6 is not selected the picture is inserted in the cell that is selected on the specific page. Can help me add a line that makes it automatically go to that cell on each page before the picture is selected? I tried and haven't had any luck.

I also had to go in and make sure that each page had the exact cell widths and height before it made all the pictures the same size with no distortion.

So Close, Thanks a million!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Picture Insert Macro Question
Replies
1
Views
69
NdNoviceHlp
NdNoviceHlp
F
  • Question
Formula to insert a picture based on various cell values, w/out VBA, Macros, etc.
Replies
4
Views
113
Domenic
D
S
  • Solved
Inserting Picture (macro) - size and position
Replies
3
Views
464
Domenic
D
U
  • Solved
Creating Workbooks with Page Breaks
Replies
5
Views
111
UT_CL
U
T
  • Question
VBA- Copy & Paste Range into Outlook as a Picture
Replies
1
Views
115
Domenic
D

Forum statistics

Threads
1,214,942
Messages
6,122,367
Members
449,080
Latest member
Armadillos

Share this page

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