How to fix error "424" object required.

Jennifer Liu
Jennifer Liu used Ask the Experts™
on
I have the following piece of code and it keeps generating error message as in the title. Can anybody help please?

 
With ActiveSheet.UsedRange
        .Value = .Value
    End With
    With NewWB
        For Each Shp In WS2.Shapes
            Shp.Delete
            ActiveWorkbook.SaveAs Filename:=FName
            .Close savechanges:=True
        Next Shp
    End With

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
With ActiveSheet.UsedRange
        .Value = .Value
    End With
    With NewWB
        For Each Shp In WS2.Shapes
            Shp.Delete
        Next Shp
        .SaveAs Filename:=FName
        .Close savechanges:=True
    End With

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
It doesn't seems to be full code but for piece of code try below:
With ActiveSheet.UsedRange
    .Value = .Value
End With
With NewWB
    For Each Shp In Sheets(1).Shapes
        Shp.Delete
        ThisWorkbook.SaveAs Filename:=FName
        .Close savechanges:=True
    Next Shp
End With

Open in new window

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
What is the purpose of:

.Value = .Value

And where are NewWB and WS2 set, I don't see that happening?

~bp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Replace above code with:
With ActiveSheet.UsedRange
        .Value = .Value
End With
With NewWB
        WS2.DrawingObjects.Delete
        ActiveWorkbook.SaveAs Filename:=FName
        .Close savechanges:=True
End With

Open in new window

Author

Commented:
Thank you so much Shum for your valuable input into solving my problems with VBA code. Now I can run my reports seamlessly with the command button working in the worksheet.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Jennifer! Glad I was able to help you :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial