Vba open workbook and paste from another workbook

hi can you please help me with this code keep getting the error "object variable or with variable not set"

many thanks

Code:

---


Sub test()
 
Dim fname As String
Dim fpath As String
Dim owb As Workbook
 
fname = "DummyXXAC.xlsm"
fpath = "C:\Users\USER\Desktop"
owb = Application.Workbooks.Open(fpath & "\" & fname)
Windows("CNAV.xlsm").Activate
Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Ccy", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks(DummyXXAC.xlsm).Sheets("Sheet1").Range("d2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats

End Sub

---

Try changing this line:
owb = Application.Workbooks.Open(fpath & "\" & fname)
to:
Set owb = Application.Workbooks.Open(fpath & "\" & fname)

also the following line may not work:
Set rngPaste = Workbooks(DummyXXAC.xlsm).Sheets("Sheet1").Range("d2")
so make it either:
Set rngPaste = Workbooks("DummyXXAC.xlsm").Sheets("Sheet1").Range("d2")
or:
Set rngPaste = Workbooks(fname).Sheets("Sheet1").Range("d2")
All untested.

You are a Genius thank youuuuu soooooo much. It was really helpful :):)

hi,

could you please help me with this code, error say invalid use of property, many thanks

Code:

---

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

---

Code:

---

Windows("CNAVIACB" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd")).Activate

---

?

Quote:

---

Originally Posted by p45cal

Code:

---

Windows("CNAVIACB" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd")).Activate

---

?

---

hi p45 thanks for your reply but you see the name of the file is CNAV IACB yyyymmdd, there is a space between the words CNAV and IACB.

should i still proceed waiting for your advice many thanks :)

added CNAV and IACB and yyyymmdd

hi p45 thanks for your reply but you see the name of the file is CNAV IACB yyyymmdd, there is a space between the words CNAV and IACB and yyyymmdd

should i still proceed waiting for your advice many thanks

Code:

---

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

---

hi p45 i have tried yur code but it says compile error invalid use of property, can help me on this please thanks :)

You're going to have to do some detective work.
When you get the error, in the Immediate pane of the vbe (Ctrl+G ifyou can't see it), type or copy:
?Sheets("Main").Range("C6").Value
and press Enter. Do you see the contents of cell C6 as you expect? (Make sure that when you do this the workbook that would normally be active at this juncture in the code, is the active workbook (the sheet Main shoulkd be in the active workbook).)

If at this point you get the same error as before, put an apostrophe at the extreme left of the highlighted line of code to comment it out temporarily, and try again.

If all OK, then enter the following in the Immediate pane:
?"]" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & "["
and press Enter. Does the result (the date part only of the filename) look right between the ] and [ characters?

If so then enter:
?"]" & "CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & "["
Press Enter. Does the full file name look right? If so add .xls, or .xlsm or whatever the file name extension is, as follows:
?"]" & "CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx" & "["
Press Enter. Does it look right?
If so enter the following:
Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate
and press Enter. Does it activate that workbook/window?

By now you may well have solved the problem..

hi, thanks for your reply this part gave me the correct date ?Sheets("Main").Range("C6").Value

but ?"]" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & "[" gave me run time error 6 overflow

could you please help me with this thanks

What does
?Clng(Sheets("Main").Range("C6").Value)
give?
I'm thinking that your date maybe a string rather than a real Excel date (a number).

hi thank you so much:) i got your point yes it was a string - the date, just one question when i had written this

?"]" & "CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx" & "[" and pressed enter i got the desired result
]CNAV IACB 20150218.xlsx[

just wanted to know what it did, i mean was it debug because in your post no.10 at last you said to enter

Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate this was the same one when i had first input before contacting you.

Quote:

---

Originally Posted by rohan8510

[/B]Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate this was the same one when i had first input before contacting you.

---

Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate << mine
Windows ("CNAV" & "" & "IACB" & "" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").Activate) << yours. Note the last close parentheses position (and the inclusion of .xlsx).

After some experimentation here it seems that even though the date is a string, Format seems to interpret it, although in some cases, depending on your locale, it might get the month and day mixed up if the date string is of the format 12/11/2015 which could be interpreted as 11 December or the 12th of November.

So you're all sorted now?

Quote:

---

Originally Posted by p45cal

Windows("CNAV IACB " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd") & ".xlsx").activate << mine
Windows ("CNAV" & "" & "IACB" & "" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").Activate) << yours. Note the last close parentheses position (and the inclusion of .xlsx).

After some experimentation here it seems that even though the date is a string, Format seems to interpret it, although in some cases, depending on your locale, it might get the month and day mixed up if the date string is of the format 12/11/2015 which could be interpreted as 11 December or the 12th of November.

So you're all sorted now?

---

yes, Thank you so much for your help:) i have started to learn so much from you regarding vba :)

hi p45, i have this code every time it says run time error 1004 application defined or object defined error. I am trying to run another macro in another workbook from my current workbook. many thanks :)

Code:

---

Workbooks("DummyIACB.xlsm").Application.Run& "macro1"

---

try:

Code:

---

Application.Run "DummyIACB.xlsm!macro1"

---

Quote:

---

Originally Posted by p45cal

try:
[CODE]Application.Run "DummyIACB.xlsm!macro1"[/CODE

---

hi p45 i tried this but it says run time error 1004 application defined or object defined error. i am actually trying to call the macro in another workbook named DummyIACB.xlsm and it is placed in "This workbook" in the file DummyIACB.xlsm.the name of the file from which iam trying to call is CNAV IACB 20150218.xls many thanks

iam trying to call

Code:

---

---

Code:

---

Sub macro1()
Sheets("Sheet1").Activate
Range("a40").Select
   
End Sub

---

try:

Code:

---

Application.Run "DummyIACB.xlsm!ThisWorkbook.macro1"

---

Thanks so much this worked :) you are in uk? isnt it? by the way my name is Rohan 31 year old male from city Kolkata in India.

hi pk, this code i have i am not able to figure out why it says object variable or with block variable not set could you please take a look, many thanks

Code:

---

Windows("CNAV.xlsm").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

---

hi pk, this code i have i am not able to figure out why it says object variable or with block variable not set could you please take a look,
the error pops at third line, when i close all the workbooks and open only the workbook which has this code it works fine, many thanks

Code:

---

---

Code:

---

Windows("CNAV.xlsm").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

---

hi p45, this code i have i am not able to figure out why it says object variable or with block variable not set could you please take a look,
the error pops at third line, when i close all the workbooks and open only the workbook which has this code it works fine,
i have tried to add Dim rngcopy As Object but it did not work, many thanks

Code:

---

Dim rngcopy As Object
Windows("CNAV.xlsm").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

---

hi p45, this code i have i am not able to figure out why it says run time error 91 "object variable or with block variable not set" could you please take a look,
the error pops at third line, when i close all the workbooks and open only the workbook which has this code it works fine,
i have tried to add "Dim rngcopy As Object" but it did not work, many thanks

Code:

---

---

Code:

---

Windows("CNAV.xlsm").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



---