Closed Thread
Results 1 to 6 of 6

Run-time error 1004 Table Cannot Overlap A Range...

  1. #1
    Registered User
    Join Date
    04-10-2009
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Question Run-time error 1004 Table Cannot Overlap A Range...

    I used the macro recorder in Excel 2007 to record the code below. The code is supposed to use a .iqy to pull data from a SharePoint 2007 site. When I run the macro, I receive the following error: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. I have no issue running the .iqy query from excel without using the macro. Any idea what's causing the 1004 error?
    Please Login or Register  to view this content.
    Last edited by romperstomper; 12-21-2010 at 10:39 AM. Reason: add tags

  2. #2
    Registered User
    Join Date
    12-22-2011
    Location
    Los Angeles
    MS-Off Ver
    2007
    Posts
    1

    Re: Run-time error 1004 Table Cannot Overlap A Range...

    Wow... I had this exact same problem just now.

    I used this code instead:
    wksData.Range("B1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    The second line I generated using the macro recorder, then selecting "Table tools" from the ribbon and clicking on refresh. When I refreshed by rightclicking, selecting edit query, then clicking OK, i got code similar to yours and I got the same error when trying to run the macro. But the simpler .Refresh method seems to work.

    Edit: OK, the wow is not worth as much - thought you posted this yesterday, not last year. lol.
    Obligatory xkcd:
    http://xkcd.com/979/

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Run-time error 1004 Table Cannot Overlap A Range...

    Hello and welcome to the forum. Please make sure to add code tags around your code as per the forum rules.

    Also would this be a shorter version of your code:

    Please Login or Register  to view this content.
    You often don't need to use select a range before doing something to it.

    Hope this helps.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  4. #4
    Registered User
    Join Date
    04-16-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Run-time error 1004 Table Cannot Overlap A Range...

    I m also having the same problem. However I couldn't get it working with your line of code
    Please Login or Register  to view this content.
    I didn't get where to make change. Here's my code
    Please Login or Register  to view this content.
    Any help is GREATLY APPRECIATED! Thanks in adv!
    Last edited by dearnemo385; 05-10-2012 at 11:27 AM.

  5. #5
    Registered User
    Join Date
    04-16-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Run-time error 1004 Table Cannot Overlap A Range...

    Noone to point me to right direction??????

    I am eagerly waiting on this. Please help me out.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Run-time error 1004 Table Cannot Overlap A Range...

    Dearnemo385,

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1