Object variable not set

[rohan8510]

hi, i keep getting object variable not set in the below code then i have to close the workbook and reopen again and then it works i have tried Dim rngcopy As Range and Dim rngPaste As Range but it did not work


Do i have to go to tools - references and select any option i see that visual basic for applications is already selected there is also another option vbaproject but iam not able to select that because it says "cannot select as that already exist" or do i have to change something in code? error pops at


Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)


many thanks

Sub test()






Dim fname As String
Dim fpath As String
Dim owb As Workbook
Dim rngcopy As Range
Dim rngPaste As Range


 
fname = "DummyIACB.xlsm"
fpath = "J:\HSS\Trad NAV-GAV\BAU Ops\IMS Asia\Valuation Packs\SNG\Blackrock\Working\CLIENT COPY TEMPLATES"
Set owb = Application.Workbooks.Open(fpath & "\" & fname)




Windows ("CNAV IACB "& Format(Sheets("Main").Range("C6").Value, "yyyymmdd" & ".xls").Activate)




Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks("DummyIACB.xlsm").Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
     
End Sub

[Aflatoon]

It probably means your Find is failing - you should always test if the range is Nothing before using it. Personally, I'd use Match here:

Sub test()

    Dim fname                 As String
    Dim fpath                 As String
    Dim owb                   As Workbook
    Dim wbActive              As Workbook
    Dim vMatch
    Dim rngcopy               As Range
    Dim rngPaste              As Range


    fname = "DummyIACB.xlsm"
    fpath = "J:\HSS\Trad NAV-GAV\BAU Ops\IMS Asia\Valuation Packs\SNG\Blackrock\Working\CLIENT COPY TEMPLATES"


    Set wbActive = ActiveWorkbook


    Set owb = Application.Workbooks.Open(fpath & "\" & fname)


    With wbActive.Sheets("Raw data")
        vMatch = Application.Match("Accrual excl XD+3", .Range("A1:IV1"), 0)


        If Not IsError(vMatch) Then
            Set rngcopy = .Range(.Cells(2, vMatch), .Cells(.Rows.Count, vMatch).End(xlUp))
            Set rngPaste = owb.Sheets("Sheet1").Range("l2")
            rngcopy.Copy
            rngPaste.PasteSpecial xlPasteValues
            rngPaste.PasteSpecial xlPasteFormats
        End If
    End With
End Sub

[rohan8510]

Thank you so much this works great thanks a lot