rohan8510
03-11-2015, 11:29 PM
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
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