Coming soon - Get a detailed view of why an account is flagged as spam!
view details

This post has been de-listed

It is no longer included in search results and normal feeds (front page, hot posts, subreddit posts, etc). It remains visible only via the author's post history.

3
[EXCEL] Difficulty locating source of 1004 error
Post Flair (click to view more posts with a particular flair)
Author Summary
boycott_nestingdolls is in Excel
Post Body

When running a code to create a new "clean" copy of a worksheet (deleting the sheet code in the copy), I continue to get a "Run-time error '1004': Method 'Range' of object '_Global' failed"

The dialogue box does not give me an option to debug, so I am at a loss as to how to fix this. My usual google solution searching has lead to nothing useful since this is such a general error.

Any ideas as to what is triggering this error?

    Sheets("ORIGINALPAGE").Select
    Sheets("ORIGINALPAGE").Copy After:=Sheets(4)
    Sheets("ORIGINALPAGE (2)").Name = "COPYOFORIGINAL"
    Sheets("COPYOFORIGINAL").Unprotect
    Sheets("COPYOFORIGINAL").Select
    Columns("J:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Dim cntOfLines As Long

cntOfLines = Application.VBE.VBProjects(1).VBComponents("Sheet2").CodeModule.CountOfLines
Application.VBE.VBProjects(1).VBComponents("Sheet5").CodeModule.DeleteLines 1, cntOfLines
    Sheets("COPYOFORIGINAL").Select
    Columns("O:AR").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

End Sub

Thanks for any help or suggestions.

--

EDIT: Thank you to the users who offered to take a close look and help me solve this one.

Here is one solution that has proved successful from /u/sooka. I changed the last section to:

    Columns("O:AR").Select
    Application.EnableEvents = False
    Selection.Delete Shift:=xlToLeft
    Application.EnableEvents = True

While I had thought deleting the worksheet code would be enough, apparently it was not. Adding this disabled the event code and allowed the columns to be deleted.

Author
Account Strength
100%
Account Age
10 years
Verified Email
Yes
Verified Flair
No
Total Karma
37,681
Link Karma
3,552
Comment Karma
33,788
Profile updated: 4 days ago
Posts updated: 1 month ago

Subreddit

Post Details

Location
We try to extract some basic information from the post title. This is not always successful or accurate, please use your best judgement and compare these values to the post title and body for confirmation.
Posted
4 years ago