Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts

    Run Time error 424 Object Required

    Hey all,

    Was trying to make a script for cleverly importing some data but I'm getting run time error 424 from it and I can't figure out why!

    Code:
    Sub GetData()
    '
    ' GetData Macro
    '
    
    '
    Dim StartRow As String
    Dim LastRow As String
    Dim DataRange As Integer
    Dim Data As String
    StartRow = 2
        Workbooks.Open Filename:= _
            "Z:\Data Analyst's Reports\NEW COOP\Coop Test Report.xlsx"
        LastRow = ActiveSheet.Cells(Cells.Rows.Count, "A").End(xlUp).Row
        Let CopyRange = "F" & StartRow & ":" & "I" & LastRow
        Let WORange = "A" & StartRow & ":" & "A" & LastRow
        Let Data = "B" & StartRow & ":" & "K" & LastRow
        Range("C:C,E:E,G:O,Q:AC,AD:AF,AH:AH,AJ:AL,AN:AN,AP:AV,AX:AX").Delete Shift:=xlToLeft
        Range("M2").FormulaR1C1 = "1"
        Range("M2").Copy
        Range(CopyRange).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
        Selection.NumberFormat = "m/d/yyyy h:mm"
        Range(WORange).Select
        Range("M2").Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("A:A").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("A2").Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[1],'[COOP Spreadsheet Test.xlsm]Sheet1'!C5:C6,1,FALSE)"
        Range("A2").Select
        Selection.AutoFill Destination:=Range(WORange)
        Range("A1").Value = "Lookup"
        Range("A1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$A$2000").AutoFilter Field:=1, Criteria1:="#N/A"
        Range(Data).Copy
        Windows("COOP Spreadsheet Test.xlsm").Activate
        'Paste at the bottom of the spreassheet
        Let DataRange = ActiveSheet.Cells(Cells.Rows.Count, "E").End(xlUp).Row
        If DataRange < 12000 Then
        Sheet1.Range("E65536").End(x1Up).Offset(1, 0).PasteSpecial _
        Paste:=x!PasteValues
        Application.CutCopyMode = False
        End If
    End Sub
    Above you can see the code. I have to admit it's not quite finished yet but the bit that it's throwing up an error on is underlined.

    Does anyone know why this might be? I've checked and I'm sure I've defined all objects :S

    Thanks a lot

    R

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Do you have a sheet whose codename is Sheet1? Also note it's XLUP not X1UP and XLPASTEVALUES not X!PASTEVALUES (capitals for clarity )
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Yeah I've got a sheet that is named Sheet1 at the moment. I guess that's what I get for thinking that it would be quicker to type than copy and paste!

    The issue seems to have been resolved. Thanks a lot Rory, you're a star

    R

Posting Permissions

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