Vba open workbook and paste from another workbook

[rohan8510]

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

many thanks

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

[p45cal]

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.

[rohan8510]

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

[rohan8510]

hi,

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

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

[p45cal]

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

?

[rohan8510]

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

[rohan8510]

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

[p45cal]

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

[rohan8510]

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

[p45cal]

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..

[rohan8510]

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

[p45cal]

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).

[rohan8510]

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.

[p45cal]

[/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?

[rohan8510]

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

[rohan8510]

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

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

[p45cal]

try:

Application.Run "DummyIACB.xlsm!macro1"

[rohan8510]

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

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

[p45cal]

try:

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

[rohan8510]

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.