Hello guys,
I currently have a macro that inserts named ranges into a shared workbook, it is shared because I have different sales people inserting information onto it, each with their own user interface file which is a macro enabled excel.
My boss wants this to work instantly, as of right now every time the macro runs it takes a couple of minutes to finish. I'm fairly new to VBA and have learned what little skills I have by reading through forums and google.
To put things into context, this is for creating a quote database, the macro works as follows:
1. Cases Interface to make sure required fields are inserted otherwise it exits sub.
2. Removes sheet protection, disables events and screen updating.
3. Opens database workbook (shared excel file).
4. Goes to last value in column A which is a formula, copies it and pastes it in the next empty cell of column A. This is a simple way to generate a "quote number."
5. Goes back to user interface and pastes the new quote number.
6. Goes back to database and loops named ranges.
7. Goes back to user interface and copies dimensions for quote, which vary because we work with cargo.
8. Goes back to database and pastes dimensions into different sheet, each dimension has a reference number that's basically a concatenate of the quote# and the piece #, ie. quote # 30 and piece # 25 =30x25.
7. Saves workbook and closes it.
8. Goes back to user interface.
9. Copies sheet.
10. Creates temp file.
11. Pastes cells.
12. Sends e-mail via outlook.
13. Done.
Now for the counterpart, we actually have an interface of our own that works with lookups of the database by using the quote number that was generated to work on the quote and e-mail it back.
Now the issues I have are:
1. Excel is taking too long, probably because my code is rudimentary.
2. I would rather export the named ranges to Access but I've read a lot about doing this and am very confused.
3. If I export it to access I would need a different way to generate quote numbers and to also look up all the information on my user interface.
So if anyone could point me in the right direction in terms of exporting named cells into an access database which I've been told should work much quicker and has less possible issues, then I'd greatly appreciate it.
I currently have a macro that inserts named ranges into a shared workbook, it is shared because I have different sales people inserting information onto it, each with their own user interface file which is a macro enabled excel.
My boss wants this to work instantly, as of right now every time the macro runs it takes a couple of minutes to finish. I'm fairly new to VBA and have learned what little skills I have by reading through forums and google.
To put things into context, this is for creating a quote database, the macro works as follows:
1. Cases Interface to make sure required fields are inserted otherwise it exits sub.
2. Removes sheet protection, disables events and screen updating.
3. Opens database workbook (shared excel file).
4. Goes to last value in column A which is a formula, copies it and pastes it in the next empty cell of column A. This is a simple way to generate a "quote number."
5. Goes back to user interface and pastes the new quote number.
6. Goes back to database and loops named ranges.
7. Goes back to user interface and copies dimensions for quote, which vary because we work with cargo.
8. Goes back to database and pastes dimensions into different sheet, each dimension has a reference number that's basically a concatenate of the quote# and the piece #, ie. quote # 30 and piece # 25 =30x25.
7. Saves workbook and closes it.
8. Goes back to user interface.
9. Copies sheet.
10. Creates temp file.
11. Pastes cells.
12. Sends e-mail via outlook.
13. Done.
Now for the counterpart, we actually have an interface of our own that works with lookups of the database by using the quote number that was generated to work on the quote and e-mail it back.
Now the issues I have are:
1. Excel is taking too long, probably because my code is rudimentary.
2. I would rather export the named ranges to Access but I've read a lot about doing this and am very confused.
3. If I export it to access I would need a different way to generate quote numbers and to also look up all the information on my user interface.
So if anyone could point me in the right direction in terms of exporting named cells into an access database which I've been told should work much quicker and has less possible issues, then I'd greatly appreciate it.
