Macro to search external file for a value and copy each row of data found

I'm a newbie to the forum and pretty weak with VBA. I've already found lots of great examples here which have been very helpful. I've been working on this current problem for too long now so I'm hoping someone will help me out.

I am creating a report/form that pulls various bits of data from an external Excel file based on one input value. One of the sheets (LatestBOM) in the external data file has values in the first column that can repeat several times. I want to copy each row that starts with a value matching the one typed into the form. Hopefully the attached files explain it better.

My strategy... Rather than loop through every row I tried to use the Match function to find the first row that the value shows up. Then my plan was to copy that row's data and use a Where Loop to look at the next row to see if the value still matched, then copy that row and so on. I can't seem to get it to work and my code is too messy to post. Any help is appreciated.

SampleData.xlsm
SampleForm.xlsm

- Ian

following will find data in criteria cell in LatestBOM and loop through rows in LatestBOM

Please Login or Register  to view this content.

Thank you so much for your reply. I should be able to figure out what to put in the Do While loop.

I noticed that the SampleData.xlsm file must be open. How can the file be accessed by the macro in a way that's invisible to the user? Of course in the real Excel files the data file is much bigger and the forms will be used 1000's of times, so I want to cut down on steps.

Thanks!

- Ian

Amazing! I would have never thought it could be done without VBA. I had to read up on dynamic name ranges, but I think I get it.

It appears that you still need to open the SampleData.xlsx file for it to work, correct?

Also, I like that a macro needs to be run by the user so data doesn't update automatically. I was planning to add a button to run the macro. Any ideas on this? I'm pretty sure that switching calculations from automatic to manual is a global setting... not just the workbook. Are Workbook Connections useful in this situation?

- Ian

This will check to see if SampleData is open, and if not, open it.
I am having an issue on trying to close SampleData when you close SampleForm. You don't want to close SampleData at the end of the macro or it takes too much time.
Perhaps someone else can assist on this. I think that it should be in the Workbook_BeforeClose event, but I can't get it to work.
You need to change sDataFile to the appropriate path/filename

Please Login or Register  to view this content.

hectop,

Thank you again. I am still wondering if the file can be opened and closed silently.

- Ian

Leith,

I decided to use the non-macro method you gave above. It looks like the source file (sampledata.xlsx) must be open for this to work. Is this correct or did I miss something?

- Ian

It is a bit of an issue but I think I worked around it. I changed the formulas such that the Names are not used and it seems to work without opening the source file. Oh, and I also got ride of the Countif() formula as it seemed like it required the source file to be open too.

Thanks for all your help! I'm sure I will reply if I get stuck again.

- Ian