Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to remove images between range of up to 100 sheets

0

I have gotten stuck attempting to use a macro to remove all images between range of sheets across multiple workbooks. There are over 100 sheets per workbook but for the sake of this exercise I have simplified the extent of which I want to remove. Obviously I am going to perform this task individually for each workbook and could do with some help finishing my macro.

This is what i have so far.

Sub Picdelete()
    Dim ws As Worksheet
    Dim sh As Shape
    Worksheet = Sheets(Array("Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18"))
    pictop = Selection.Top
    picleft = Selection.Left
    precision = 10   'set to 0 if the pictures are EXACTLY in the same place on each sheet
    For Each ws In Worksheets
      For Each sh In ws.Shapes
        If Abs(sh.Top - pictop) <= precision And Abs(sh.Left - picleft) <= precision Then
          sh.Delete
        End If
      Next sh
    Next ws
End Sub
excelmacros-vba
posted by: Dewbo1234 on Fri Jun 23, 2017 at 5:42 am
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1989) Jul 2, '17 at 11:46 am
Add to Discussion

Answers

0

Try this.. [ GIVE CREDIT if this works for you Please!!].

    Make sure to back up first!

Sub RemovePics()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim p As Object 'pictures
  
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
  For Each ws In ActiveWorkbook.Worksheets
  
    For Each p In ws.Pictures
        p.Select
        'MsgBox p.Name
        p.Delete
    Next p
  Next ws
    Range("A1").Select
    
    Set p = Nothing
    Set ws = Nothing
    Set wb = Nothing
    
    MsgBox "Operation Complete! " & Chr(13) & "All pictures deleted!", vbInformation, "Finished!"
    
End Sub
posted by: queue (rep: 467) on Fri Jun 23, 2017 at 3:32 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Discuss

Discussion

This operation would be applied to all sheets in the active workbook. I need the macro to apply to a select number of sheets (which can be listed or can be a range) only.
Dewbo1234 Jun 26, '17 at 3:29 am
Add:
Dim Worksheets as Variant
to the top

And then add:
Worksheets = Array("Sheet1","Sheet3","Etc")

And change:
ActiveWorkbook.Worksheets
to
Worksheets
don (rep: 1989) Jun 26, '17 at 4:19 am
Don't forget the CODE tags queue; I put some in for you so it's easier to read.
don (rep: 1989) Jun 26, '17 at 4:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login