VBA Lookup errors

**dontich** · 06-18-2012, 11:01 AM

I am writing a macro that needs to look up data in a different workbook in the first sheet with a particular name. I need to copy all the data from the sheet into a select worksheet in a select workbook.

I have the following code so far.

Please Login or Register  to view this content.

It works fine with debugging however when I try to copy the information from the files it does not copy but makes a new version of it called Book3. then it closes the original. this is fine but not really useful...
I am sure i am making a silly mistake can anyone find it?

**wallyeye** · 06-18-2012, 11:21 AM

What do the different parameters represent? It looks like you are referring to the active workbook/worksheet and the workbook/worksheet you are opening, there might be a better way to reference them.

Something like:

Please Login or Register  to view this content.

**Leith Ross** · 06-18-2012, 11:21 AM

Hello dontich,

Welcome tot he Forum!

Try this revision of your macro and let me know the results.

Please Login or Register  to view this content.

**dontich** · 06-18-2012, 11:31 AM

Originally Posted by wallyeye

What do the different parameters represent? It looks like you are referring to the active workbook/worksheet and the workbook/worksheet you are opening, there might be a better way to reference them.

Something like:

Please Login or Register  to view this content.

Hmm this does not work. The line
set wbkloco = application.workbooks.open filename:=loconame
returns an error that end of line is exspected, I think it is because I can not set a workbook when I am opening it?

**dontich** · 06-18-2012, 11:32 AM

Originally Posted by Leith Ross

Hello dontich,

Welcome tot he Forum!

Try this revision of your macro and let me know the results.

Please Login or Register  to view this content.

Hmm this would work if the other file is open beforehand I am having trouble adding that into your code

**dontich** · 06-18-2012, 11:33 AM

Also, Thanks alot guys!,, i know alot of excel formula stuff but am not amazing at VBA yet so I will try to post on the forums section and help other people out there.