Announcement

Collapse
No announcement yet.

Run-Time Error '1004': application defined or object defined error

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run-Time Error '1004': application defined or object defined error



    I new to VB but I have come across this error code and I'm not sure what is going wrong. Here is the code I am using. I'm basically moving a filtered data dump from SQL to another tab using the select visible cells function and copying and pasting that way. Any help would be greatly appreciated.
    Last edited by cytop; November 9th, 2011, 07:56. Reason: Removed HTML tags

  • #2
    Re: Run-Time Error '1004': application defined or object defined error

    Please edit your post to include code tags.

    I cleaned it up a bit. What line are you getting the error on?
    Code:
    Sub ExtractData()
    '
    ' ExtractData Macro
    '
    '
    Rows("2:2").Select
    Range("G2").Activate
    Rows("2:198457").Select
    Range("G2").Activate
    Selection.Delete Shift:=xlUp
    ActiveSheet.ListObjects("Table_sql_Reports_Data"). _
    Range.AutoFilter , Field:=59, Criteria1:="DPN"
    Cells.Select
    Range("Table_sql_Reports_Data[[#Headers],[ClosedStatus]]"). _
    Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("BC1").Select
    Range("A1").Select
    ActiveSheet.Paste
    End Sub
    Last edited by Eggcel; November 9th, 2011, 01:26. Reason: typo

    Comment


    • #3
      Re: Run-Time Error '1004': application defined or object defined error

      I think its on this line.

      ActiveSheet.ListObjects("Table_sql_Reports_Data"). _
      Range.AutoFilter Field:=59, Criteria1:="DPN"

      Comment


      • #4
        Re: Run-Time Error '1004': application defined or object defined error

        Please see here http://www.ozgrid.com/forum/misc.php?do=bbcode#code for the proper use of code tags and edit your posts.

        Is it possible to attach your workbook? It doesn't look like you define any range before the autofilter, but it's hard to know what you are doing without seeing it.

        Comment


        • #5
          Re: Run-Time Error '1004': application defined or object defined error

          How do I find which line is giving me the error? I thought it was the ones that highlight in yellow when you hit Debug.

          Comment


          • #6
            Re: Run-Time Error '1004': application defined or object defined error

            It is.

            Comment


            • #7
              Re: Run-Time Error '1004': application defined or object defined error

              Here is the workbook, sort of. I couldn't include everything because of proprietary reasons but all the code is there. I think the range you are talking about is highlighted in red now.
              Attached Files

              Comment


              • #8
                Re: Run-Time Error '1004': application defined or object defined error

                Code:
                Sub ExtractData()
                 '
                 ' ExtractData Macro
                 '
                 
                '
                 
                 Rows("2:198457").Select
                 Range("G2").Activate
                 Selection.Delete Shift:=xlUp
                 ActiveSheet.AutoFilterMode = False
                 
                 ActiveSheet.ListObjects("Table_sql_Reports_Data"). _
                 Range.AutoFilter Field:=59, Criteria1:="DPN"
                 Cells.Select
                 Range( _
                 "Table_sql_Reports_Data[[#Headers],[ClosedStatus]]"). _
                 Activate
                 Selection.SpecialCells(xlCellTypeVisible).Select
                 Selection.Copy
                 Sheets("Sheet2").Select
                 Range("BC1").Select
                 
                 Range("A1").Select
                 ActiveSheet.Paste
                 End Sub
                See if this helps. It will remove autofilter if already applied. Shot in the dark since your attached workbook has no data.

                Comment


                • #9
                  Re: Run-Time Error '1004': application defined or object defined error

                  Thanks for your help. That did not work but I appreciate you taking the time to try. Unfortunately I can not share the data. Basically what the first module is a pull of data out of an SQL server. In the second module, I am trying to pull out certain data that has only been filtered by one category and copy and paste that data on to another worksheet but when I do that, I get the run time error. I would like to incorporate this as the data moves and calculates faster than when it is the actual data coming from the SQL server.

                  Comment


                  • #10


                    Re: Run-Time Error '1004': application defined or object defined error

                    I was just looking at your code again, and I notice you are deleting a bunch of data before the autofilter
                    Code:
                     Rows("2:198457").Select
                     Range("G2").Activate
                     Selection.Delete Shift:=xlUp
                    where on the sheet is the listobject? does it get deleted prior to the .autofilter?

                    Comment

                    Working...
                    X