Using a closed MS Excel file in Macros

[mhraja]

I am using Advance Filter to pull data from one file [File name:AVGUSAGE.xls Sheet Name:AVGUSAGE] to another file[FilterMacro.xls].

I recorded the Marco for the advance filter. The code of that Macro is as follow:

Please Login or Register  to view this content.

The Macro runs fine as long as the sheet AVGUSAGE (from which I am pulling the data) is opened. When the sheet is closed the macro gives an error.

I know I need to open that Sheet in Marco VB code. I am not at all familiar with VB but I somehow got to know how to open the file before program run and close it after it.

After adding the open file code, my MARCO code looks like:

Please Login or Register  to view this content.

When I run my macro now it gives me an error:

Run-time error ‘1004’: The exact range has a missing or illegal field name.

Being a total stranger to VB, I have no idea where have I gone wrong.

Do I have to declare workbook and worksheets and then use variables?

Kindly help! My sheet can be downloaded from:

http://rapidshare.com/files/410985318/AVGUSAGE.zip

[romperstomper]

Which sheet and workbook are the criteria and destination ranges in?

[mhraja]

Which sheet and workbook are the criteria and destination ranges in?

Thanks for the reply.

Data to be pulled (List Range) is in File:AVGUSAGE.xls Sheet: AVGUSAGE

The criteria and destination fields are in File: FilterMacro.xls. Sheet: Sheet1

You can download the files from the link:

http://rapidshare.com/files/410985318/AVGUSAGE.zip

You will notice as long as both workbooks are open the macro will work. When you will close workbook AVGUSAGE, macro will stop working.

Thanks

[romperstomper]

I'm afraid I won't download files from there - you should attach them here instead.

Try this:

Please Login or Register  to view this content.

[mhraja]

Its working perfect.

Thanks a million