Solved: Help on deleting previous pict before placing new one vba

[VISHAL120]

Hi All,

Am actualy using the following codes to unhide sheets and place picture on the different sheets available.
But before placing the picture the previous one shall be deleted otherwise it places the new picture on the previous one which increases the size of the files and also distort the format on the sheets.

Everytime am running the code it got debug with error like " Item with the specifc name was not found.

i guess its changing name evertime its coping . Am using Rectangle shapes to assign picture by Selecting ( Format, Color,Fill Effects,Picture).

Private Sub hide_sheets()

Application.ScreenUpdating = False
Sheets("prod").Visible = False
Sheets("Samples").Visible = False
Sheets("Despatch").Visible = False


Application.ScreenUpdating = True

End Sub
Private Sub unhide_sheets()

Application.ScreenUpdating = False
Sheets("prod").Visible = True
Sheets("Samples").Visible = True
Sheets("Despatch").Visible = True


Application.ScreenUpdating = True

End Sub

Sub Place_picture()
Application.ScreenUpdating = False
'UNHIDE ALL SHEETS BEFORE DELETING OR ASSIGNING NEW PICTURE.

Call unhide_sheets
'DELETE PREVIOUS PICTURE FIRST
Call Delete_Picture


'ASSIGN NEW PICTURE ON ALL SHEETS
Sheets("MAin").Select
Range("C8").Select
ActiveSheet.Shapes("Rectangle 3").Select
Selection.Copy
Sheets("prod").Select
Range("C7").Select
ActiveSheet.Paste
Sheets("samples").Select
Range("C7").Select
ActiveSheet.Paste
Sheets("despatch").Select
Range("C7").Select
ActiveSheet.Paste
Sheets("main").Select
Range("B1:C1").Select

Application.ScreenUpdating = True
End Sub
Private Sub Delete_Picture()

Application.ScreenUpdating = False


ActiveSheet.Shapes("Rectangle 3").Select
Selection.Delete
Sheets("samples").Select
Range("B7").Select
ActiveSheet.Shapes("Rectangle 3").Select
Selection.Delete
Sheets("prod").Select
Range("B8").Select
ActiveSheet.Shapes("Rectangle 3").Select
Selection.Delete
Sheets("main").Select
Range("B1:C1").Select
Application.ScreenUpdating = True
End Sub

Am attaching a sample file also.


Can someone please guide me of how i can delete the previous one before placing a new picture.

Thanks for the help.
:banghead:

[Bob Phillips]

Private Sub hide_sheets()

Application.ScreenUpdating = False
Sheets("prod").Visible = False
Sheets("Samples").Visible = False
Sheets("Despatch").Visible = False

Application.ScreenUpdating = True
End Sub

Private Sub unhide_sheets()

Application.ScreenUpdating = False
Sheets("prod").Visible = True
Sheets("Samples").Visible = True
Sheets("Despatch").Visible = True

Application.ScreenUpdating = True
End Sub

Sub Place_picture()
Application.ScreenUpdating = False
'UNHIDE ALL SHEETS BEFORE DELETING OR ASSIGNING NEW PICTURE.

Call unhide_sheets
'DELETE PREVIOUS PICTURE FIRST
Call Delete_Picture


'ASSIGN NEW PICTURE ON ALL SHEETS
For Each shp In Worksheets("main").Shapes

shp.Copy

If shp.Name Like "Rect*" Then

Sheets("prod").Paste
Sheets("samples").Paste
Sheets("despatch").Paste
End If
Next shp

Application.ScreenUpdating = True
End Sub

Private Sub Delete_Picture()

Application.ScreenUpdating = False

For Each sh In Array("prod", "samples", "despatch")

For Each shp In Worksheets(sh).Shapes

If shp.Name Like "Rect*" Then shp.Delete
Next shp
Next sh

Application.ScreenUpdating = True
End Sub

[VISHAL120]

Hi Bob,

Thanks a lot it works perfect.