• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Loop through all worksheets, and insert a picture on each sheet - VBA

I am trying to write a macro that goes through each sheet in the workbook, and then place a picture from the hard drive in each sheet in A1, left and top justified.

It works 'somewhat' if I start from first worksheet.  It won't place the picture in the right spot in the following sheets.  Also, it crashes if I don't start in Sheet1.

Any help is appreciated!

Sub Insert_Logo()
Dim wb As Workbook
Dim sh As Worksheet
Dim PicRange As Range
Set PicRange = Range("A1")
Set wb = ThisWorkbook

For Each sh In wb.Worksheets
    
    sh.Pictures.Insert("c:\LOGO.jpg").Select
    With Selection
    Set PicRange = Range("A1")
        .Left = PicRange.Left
        .Top = PicRange.Top
        .Width = 100
    End With
Next sh

End Sub

Open in new window

0
jackharkness
Asked:
jackharkness
  • 3
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Is there any noticeable difference if you simply your code to just this?...

Sub Insert_Logo()

  Dim sh                                                As Worksheet

  For Each sh In ThisWorkbook.Worksheets
    
      sh.Pictures.Insert ("c:\LOGO.jpg")

  Next sh

End Sub

Open in new window


BFN,

fp.
0
 
jackharknessAuthor Commented:
FP - that works but doesn't position it correctly (just puts in the middle of the page).  How would I identify the jpg to now relocate it?  (and thanks for helping!!)
0
 
[ fanpages ]IT Services ConsultantCommented:
Hmmm... that's odd.  I tested in MS-Excel 2003, & in MS-Excel 2013.
I do not have MS-Excel 2007, &/or 2010 installed, to try these, unfortunately.

Which version are you using?

In each of my tests, the test logo(.jpg) I was using appeared in the top left corner of each worksheet.

To answer your question though...

Sub Insert_Logo()

  Dim sh                                                As Worksheet

  For Each sh In ThisWorkbook.Worksheets
    
      sh.Pictures.Insert ("c:\LOGO.jpg")
      
      sh.Shapes(1&).Left = 0
      sh.Shapes(1&).Top = 0

  Next sh

End Sub

Open in new window

0
 
[ fanpages ]IT Services ConsultantCommented:
Over 3.5 months later you come back, do not respond to my most recent query about which version of MS-Excel you are using, but close the question with a 'B' grading.

OK.  I'm not going to bother asking for a Moderator to re-open the question &/or for clarification of the grading applied as, frankly, I wouldn't be interested in responding even if you did reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now