• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help how to run this code in all sheets of workbook

R

Reggie Jardin

Member
Code: 
Sub Insert_Picture2()
Dim lThisRow As Long, n As String, anyShape As Shape, l As Single, t As Single
Application.ScreenUpdating = False
                  lThisRow = 18
With ActiveSheet
    Do While .Cells(lThisRow, 18).Value > ""
        With .Cells(lThisRow, 18)
            l = .Offset(, 2).Left
            t = .Top
            n =\\PicLocation\" & .Text
        End With
        If Dir(n) > "" Then
            With .Pictures.Insert(n)
                With .ShapeRange
                    .LockAspectRatio = msoFalse
                              .Height = 145
                              .Width = 100
                            .Rotation = 0
                End With
                      .Top = t
                    .Left = l
                .Placement = 1
            End With
        End If
        lThisRow = lThisRow + 1
    Loop

    .Range("R20").Select
End With

Application.ScreenUpdating = True
MsgBox "Pic Inserted!"
End Sub

Hi I want to run this code in all sheets of my workbook.. can someone modify this code to run in all sheets of the workbook as all my sheet is having same cell reference.
Thanks in advance.
 
Last edited:
Hi ,

The code works on the active sheet ; as long as you select this , you can run this macro on every worksheet in your workbook.

Only if you have a large number of worksheets do you need to incorporate a loop into the posted code.

Narayan
 
Hi Thanks For Your Prompt Reply..
Yes its working in active sheet..
But I want to work this code in all sheets of the workbook in one shot as im working in a lot of sheets in workbook and im planning to run this in batch file..so the idea is to whenever this macro run..in should run in all sheets of the workbook..

Can you re write the code and have it run in all sheets of workbook?
Thank You in advance..
 
Hi ,

See if this works ; make a backup of your workbook before you try this out.
Code: 
Sub Insert_Picture2()
    Dim wsht As Worksheet
    Dim lThisRow As Long
    Dim n As String
    Dim anyShape As Shape
    Dim l As Single, t As Single
   
    Application.ScreenUpdating = False
   
    lThisRow = 18
   
    For Each wsht In ThisWorkbook.Worksheets
        With wsht
            Do While .Cells(lThisRow, 18).Value > ""
                With .Cells(lThisRow, 18)
                    l = .Offset(, 2).Left
                    t = .Top
                    n = "\\PicLocation\" & .Text
                End With
       
                If Dir(n) > "" Then
                  With .Pictures.Insert(n)
                        With .ShapeRange
                            .LockAspectRatio = msoFalse
                            .Height = 145
                            .Width = 100
                            .Rotation = 0
                        End With
                        .Top = t
                        .Left = l
                        .Placement = 1
                  End With
                End If
                lThisRow = lThisRow + 1
            Loop
            .Range("R20").Select
        End With
    Next
    Application.ScreenUpdating = True
    MsgBox "Pic Inserted!"
End Sub
Narayan
 
HI,

Thanks , I tried it showing Select method of Range class Failed
Run Time Error '1004'

When i tried to debug its pointing on - .Range("R20").Select
 
Reggie Jardin said:
HI,

Thanks , I tried it showing Select method of Range class Failed
Run Time Error '1004'

When i tried to debug its pointing on - .Range("R20").Select
Click to expand...
Hi ,

I have no idea why this should be so , when this same line in your posted code did not generate an error.

Can you upload your workbook with at least a few worksheets ?

Narayan
 
Hi, i worked it out...

But now my problem is this code is just linking the image to excel, hence the other user cannot see it when i send the mail.

Can someone modify this code that instead of linking the image it should copy and paste the image itself in the excel so that when i send the file other user can see the image...
 
hi all masters.. i really badly help need.. what is the code fix for the code above that will put the inserted picture not as a link but a real image attached.. as if im sending this to others.. they cannot see the image as it is link on my pc..
 
You must log in or register to reply here.
Back
Top