Copy paste everything into a new sheet except excel objects

[snjpverma]

I came across an Excel sheet that takes a very long time to save and is also too large in size. Upon further investigation, I found that there are many hidden objects in the sheet that are causing it to be slow and large. When I try to select all objects, Excel hangs or gets stuck, making it impossible for me to delete them. How can I solve this issue and delete all objects from the worksheet (not the entire workbook)? Is copying and pasting everything except for Excel objects into a new sheet the only solution?
Last time I tried and found that it takes a lot of efforts to copy and paste everything , i.e. column width , formula, values, formatting, data validation, etc) into a new sheet and then rename it.

 

[moddddmyexcel]

Right click the sheet and select Move or Copy. You can leave it in the same workbook or move it to another, tick off Create a copy. Objects should not follow.

 

[snjpverma]

Objects should not follow.

That's incorrect because everything will get copied in this way, especially the shapes that's been inserted.

 

[vcoolio]

Assuming that you have a number of different objects to delete, try the following VBA code to do the task for you:

Sub DeleteShapes()

    Dim obj As Object

    For Each obj In ActiveSheet.DrawingObjects
            obj.Delete
    Next obj
    
End Sub

You will need to unhide any hidden shapes/objects first.
I hope that this helps.

Cheerio,
vcoolio.

 

[snjpverma]

Thanks for the code.
How would I copy and paste everything into another sheet except the objects?
I do it manually by paste special, but i have to paste it multiple times, once for values, then for formulas, then for formats, and so on.

 

[moddddmyexcel]

That's incorrect because everything will get copied in this way, especially the shapes that's been inserted.

Did you try this? I do this all the time and it does not copy Objects for me (macro button, images, shapes, text boxes).

 

[moddddmyexcel]

That's incorrect because everything will get copied in this way, especially the shapes that's been inserted.

My apologies this is a setting. Options > Advanced > Cut, Copy and Sort inserted objects with their parent cells.

 

[vcoolio]

Hello snjpverma,

You may only need this simple code:-

Sub TestCopy()

    Application.ScreenUpdating = False
   
    ActiveSheet.UsedRange.Copy
    Sheet2.[A1].PasteSpecial xlPasteAll  'or change your destination sheet name to suit.
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

It will copy and paste all formats, values etc. but leave behind any objects. However, I don't see the need to copy and paste to a new sheet if you only want to delete the objects in your activesheet.
The code in post #4 will do that for you.

Cheerio,
vcoolio.

 

[snjpverma]

Thanks @vcoolio for the code.

However, I don't see the need to copy and paste to a new sheet if you only want to delete the objects in your activesheet.

Actually, there are thousands of objects in the sheet causing it to get stuck. So, in case the object deletion code may get stuck due to this. So, this code in post #8 will be used in that case.

 

[vcoolio]

Hello snjpverma,

Sounds nasty!
It would be a good idea to test both codes in a copy of your actual workbook first just to see which way suits you best.

Anyway, I'm glad to have been able to assist in some way and thanks for the feed back.

Cheerio,
vcoolio.