Results 1 to 9 of 9

Thread: Why 1004 Run Time error at Red Text?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Why 1004 Run Time error at Red Text?

    Sub CopyData_Machine_Performance()
    Application.ScreenUpdating = False
    Dim lastRow As Long
    lastRow = Sheets("DATADump").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ldateto As Long
    Dim ldatefrom As Long
    Dim ThisMonth As Long
    Dim ThisYear As Long
    ThisMonth = Month(Sheets("REPORTDATE").Range("C2"))
    ThisYear = Year(Sheets("REPORTDATE").Range("C2"))
    ldatefrom = DateSerial(ThisYear, ThisMonth, 1)
    ldateto = DateSerial(ThisYear, ThisMonth + 1, 0)
    With Sheets("SLOT MACHINE PERFORMANCE")
    .Range("A4:t4") = Array("Asset Number", "Area", "Section", "Location", "Game Tpye", "Mfg", "Denom", "Theme", "Coin In", "CIPUPD", "Win", "T-Win", "Handle Pulls", "DOF", "WPUPD", "T-WPUPD", "Hold%", "T-Hold", "Avg Bet", "House Avg")
    End With

    With Sheets("DATADump").Range("A1").CurrentRegion

    .AutoFilter Field:=2, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto

    End With
    Sheets("DATADump").Activate
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("O:O")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("P:P")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("Q:Q")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("R:R")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("E:E")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("S:S")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("D:D")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("T:T")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("G:G")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("H:H")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("J:J")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("K:K")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "L").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("L:L")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0)
    Intersect(Rows("2:" & lastRow).SpecialCells(xlCellTypeVisible), Range("M:M")).Copy Sheets("SLOT MACHINE PERFORMANCE").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0)
    Sheets("SLOT MACHINE PERFORMANCE").Columns.AutoFit
    If Sheets("DATADump").AutoFilterMode Then Sheets("DATADump").AutoFilterMode = False
    Application.ScreenUpdating = True
    Worksheets("DataDump").ShowAllData
    Sheets("SLOT MACHINE PERFORMANCE").Select
    Call WPUPD
    End Sub

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,065
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    Format() your dates to strings.

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    Not sure I understand? Sorry for being such a noob

  4. #4
    New Member
    Join Date
    Jun 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    The Code was working fine until I added a new month of data. I added it the same as always??

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,059
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    Maybe the 'Current Region' isn't quite what you think it is..

    Add this line in there, and what does the message box say?
    Code:
    With Sheets("DataDump").Range("A1").CurrentRegion
        MsgBox .Address
        .AutoFilter Field:=2, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto
    
    End With
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    Msg Box Shows $A$1:$AA$101646
    My Data A1:AA15841
    My table is set A1:AA15841

  7. #7
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,065
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    If you don't know what a command word does, use F2 or put cursor in or next to the word in the VBE and press F1.

    When you record a macro with dates, you get something like:
    Code:
    ActiveSheet.Range("$A$1:$A$13").AutoFilter Field:=1, Criteria1:= _
            ">=10/5/2018", Operator:=xlAnd, Criteria2:="<=1/3/2019"
    e.g.
    Code:
    Msgbox Format(date, "mm/dd/yyyy")
    If the range is wrong maybe you want UsedRange? e.g.
    Code:
    With Sheets("DataDump").UsedRange
        MsgBox .Address
        .AutoFilter Field:=2, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto
    
    End With

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,059
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    If the message box produced a range, then the range isn't the problem (it's OK that it's 'larger than' your actual data. Smaller may have been an issue).

    So it must be the ctiteria that is the probem.
    As Kenneth Suggests, try making your dates into Text.

    Dim ldatefrom as String
    ldatefrom = Format(DateSerial(yr,mnth,day),"mm/dd/yyyy")
    Last edited by Jonmo1; Aug 6th, 2018 at 04:35 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    New Member
    Join Date
    Jun 2018
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Why 1004 Run Time error at Red Text?

    I tried the date string and got no positive results. This code has been working for awhile now so I fond it strange that the date would all of the sudden not work. The only difference between this month an last month is more data. So for now I seems to work with the change below. I guess we will see what next month brings. Thank you al for your quick response and input, I have learned a lot by asking questions here.

    With Sheets("DATADump").Range("A1").CurrentRegion
    .AutoFilter Field:=2, Criteria1:=">=" & ldatefrom, Operator:=xlAnd, Criteria2:="<=" & ldateto

    I changed the Range
    From:

    • With Sheets("DATADump").Range("A1").CurrentRegion



    To:


    • With Sheets("DATADump").Range("A1")

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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