1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Reggie Jardin, Mar 13, 2018.

  1. Reggie Jardin

    Reggie Jardin New Member

    Messages:
    24
    Code (vb):

    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: Mar 13, 2018
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,606
    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
  3. Reggie Jardin

    Reggie Jardin New Member

    Messages:
    24
    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..
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,606
    Hi ,

    See if this works ; make a backup of your workbook before you try this out.
    Code (vb):

    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
  5. Reggie Jardin

    Reggie Jardin New Member

    Messages:
    24
    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
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,606
    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
  7. Reggie Jardin

    Reggie Jardin New Member

    Messages:
    24
    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...
  8. Reggie Jardin

    Reggie Jardin New Member

    Messages:
    24
    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..
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,606

Share This Page