asked on

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

[ fanpages ]

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.

jackharkness

ASKER

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!!)

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

[ fanpages ]

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.