Carpiem
04-10-2006, 08:00 PM
Hello,
Urgently need your help to solve this puzzle. I run a macro (Report), manually copy data from imported files then delete the imported files, run another macro (Compile) and finally save the workbook.
Translation:
1) “Report” Macro:
(a) Opens a folder on my hard drive. I select some files (the number of files to be opened is not restricted, but would usually be 2) and click enter.
(b) The macro converts these into excel files (.xls) and imports/inserts them into the workbook. The (imported files) sheet names are set according to the original file path.
(c) These sheets are always inserted to the left of the “Import” sheet where I have a command button that executes or starts this macro
2) I manually copy the data from the imported sheets, as at (c) above, to sheets named “First” and “Second”, which are permanent sheets, and then delete the imported sheets.
3) “Compile” Macro:
- Copies column A of “Second” sheet and pastes it into column A of “List” sheet.
- Copies column A of “First” sheet and pastes it into column A of “List” sheet.
- Selects “List” sheet and filters column A for a unique list of part numbers.
- Copies the filtered list of part numbers to a range in the “Report” sheet.
Report | Consolidate| List | First | Second |Flow4 | Flow5 | Import
The sheets order is as above, L to R.The imported sheets being “Flow4” and “Flow5”.
I can't figure out how to do the following with vba:
Find each new instance of imported/inserted sheets. Meaning I finish working with said file(s) and save the workbook. Now I start over again, but select a different set of files as at (a) above. Well my macro crashes as I am telling it to look for files that are not only named differently but also have new sheet numbers.
It seems as if I am in a Catch22 situation here.:banghead:
Just in case someone has a solution based on the order of sheets. The sheet order would be of no importance for my application.
Your suggestions are welcomed and greatly appreciated.:help
Thank you,
Carpiem
Urgently need your help to solve this puzzle. I run a macro (Report), manually copy data from imported files then delete the imported files, run another macro (Compile) and finally save the workbook.
Translation:
1) “Report” Macro:
(a) Opens a folder on my hard drive. I select some files (the number of files to be opened is not restricted, but would usually be 2) and click enter.
(b) The macro converts these into excel files (.xls) and imports/inserts them into the workbook. The (imported files) sheet names are set according to the original file path.
(c) These sheets are always inserted to the left of the “Import” sheet where I have a command button that executes or starts this macro
2) I manually copy the data from the imported sheets, as at (c) above, to sheets named “First” and “Second”, which are permanent sheets, and then delete the imported sheets.
3) “Compile” Macro:
- Copies column A of “Second” sheet and pastes it into column A of “List” sheet.
- Copies column A of “First” sheet and pastes it into column A of “List” sheet.
- Selects “List” sheet and filters column A for a unique list of part numbers.
- Copies the filtered list of part numbers to a range in the “Report” sheet.
Report | Consolidate| List | First | Second |Flow4 | Flow5 | Import
The sheets order is as above, L to R.The imported sheets being “Flow4” and “Flow5”.
I can't figure out how to do the following with vba:
Find each new instance of imported/inserted sheets. Meaning I finish working with said file(s) and save the workbook. Now I start over again, but select a different set of files as at (a) above. Well my macro crashes as I am telling it to look for files that are not only named differently but also have new sheet numbers.
It seems as if I am in a Catch22 situation here.:banghead:
Just in case someone has a solution based on the order of sheets. The sheet order would be of no importance for my application.
Your suggestions are welcomed and greatly appreciated.:help
Thank you,
Carpiem