Macro to open a file, input value into a cell, and run a macro from file

**wkhor** · 08-10-2011, 09:17 PM

I have a worksheet with a list of Order Numbers in Column A, starting from cell A1. I want to create a macro that will loop through all the Order Numbers in Column A and for each Order Number do the following:

1. Open a file (GenerateReport.xls) in a folder path (C:\temp\)
2. Input the Order Number into cell C6 of the Data tab in GenerateReport.xls
3. Run a macro (Gen_Report) in GenerateReport.xls (this macro will create a report based on the Order Number in cell C6 and close the GenerateReport.xls file)
4. Repeat steps 1,2,3 for the next Order Number

Would really appreciate any help that I can get as I am newbie to VBA. Thank you!

**gjlindn** · 08-10-2011, 09:49 PM

Hi wkhor...Does the macro Gen_Report already exist? If so try this:

Please Login or Register  to view this content.

**Trebor76** · 08-10-2011, 10:16 PM

Hi wkhor,

Try this - just change the three variables I've commented with 'Change to suit' if necessary:

Please Login or Register  to view this content.

Regards,

Robert

**wkhor** · 08-11-2011, 03:00 AM

Thanks for the quick response Robert. The macro Gen_Report already exists.

Your code works great! The only thing i changed was adding the
Workbooks.Open Filename:=strFileName
into the For-Next loop because the Gen_Report macro closes the GenerateReport.xls file everytime the macro is run.