Hi everyone,
I have an order form I want to put onto separate worksheets from one main order sheet. However, I am having to repeat the macro I have called Order each time after clicking in the A cell of that row. Is there anyone out there that could put this into a string for me and then put the last two macros - Summary & Rename to automatically follow on once the orders sheets have been produced.
Thank you so much
If you want several macros to run one after another I would suggest one macro to call the all:
You should maybe add a test to check if Orders worked correctly and then invoke the next one, etc.
Please Login or Register to view this content.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
I. You gave us too little data.Originally Posted by Angelique Cassam
For instance, how do we know which rows (?) / cells (?) in 'A' column in the bulksheet (main sheet) you are selected to start the macro ?
It absolutely does not result from your macro.
What are the criteria for selecting a particular cell / row ?
Maybe a small example ?
II. There is too much "select" in your macro, take a look at the code below and check if it works for you:
Please Login or Register to view this content.Please Login or Register to view this content.
Thank you soooo much. I will definitely like to have one macro to call the others. I realise I did not explain myself very well so I have attached the original Excel file without orders and then
another with the order sheets done via the macros and then I did a screen shot of the process I have to go through to do this every time.
Thank you so much for all your assistance, it is very gratefully received.
Angelique
Are you sure you're on Excel 2003?
I have Office 365 for home use
That explains the xlsx and xlsm files, your profile mentions 2003
Oops, I am sorry. I have updated it now. Also living somewhere very different now too lol.
That's quite a different place than Spain
I now understand it a little better, but the recorded macros are terrible.
My assumption is that you knowledge of VBA is at least minimal.
You did not even take the trouble to edit the macros in and keep using the shift up down , well it hurts.
But then again, everyone has to learn and you cannot run with starting to crawl.
I will see if I can correct this but I do need to know for certain. The SUMMARY SHEET is the one that your Order Macro uses as source?
What do you do with the data after the orders have been created?
If you run the macro again, won't you be duplicating things?
You should always make sure that a macro runs and addresses the correct sheet. If you don't and you use ActiveSheet how is the macro to know that the ActiveSheet is the sheet it needs to address.
um yes, my VBA is very minimal. However, I can do a macro lol but want to move on from here really.
In principal I want a default blank workbook with vba called Wine. A wine order is imported into the workbook and the vba is then run.
The vba must first create the SUMMARY SHEET from the imported wine order using the Sub Summary() macro and then the Sub Rename() macro. This is the worksheet which is the source of all the data.
I have been trying to do a string for the Sub Order() macro to run for all rows with data within the SUMMARY SHEET (you are right I forgot to add a line: Sheets("SUMMARY SHEET").Select - at the end of the Sub Order() macro). Once all the order worksheets have been completed the Sub Rename() macro will run again to rename all the created worksheets.
The whole workbook is then saved under that day's date and the default workbook "Wine" is left intact.
Okay, now the story is starting to take form.
Let me summarize what I have understood form your last post and the word file which to attached too.
1. You've got a workbook with all the place orders:
This workbook is named wine list to put into orders.xlsx or maybe another name
As the name implies it does not contain any macros it's strictly a file with a number of data rows
2. For every order you first insert the vba modules and then save it as a xlsm file to process all rows seprately
3. The result is one file with an x number of worksheets one for each customer, correct?
If above is correct the my solution:
One file that serves as the masterfile to run the entire process:
1. Read the Winde Order
2. Create a new workbooks named Wine Orders - day's date and time (if you run more than on)
3. In this workbook an X number of worksheets, one for each customer.
Advantage: no more copying and pasting vba modules or creating shortcut keys
Does this appeal?
yes! that would be brilliant.
I won't make it too complicated but I do think it'll be efficient.
How do you plan to keep record of the wine order files that have been processed to avoid repetition?
Have you thought about it?
I was thinking maybe an order number to auto generate but that is a whole other ball game at the moment. I just want to stop having to do this in such a long winded way first.
You've now got three columns with Suppliers Wine 1, ...2 and ..3 can there be more?
Dear Keebellah,
My original process has been quite long. I would import the wine order list into an Excel sheet. Put in the Sub Summary(), Sub Order() and Sub Rename() macros into a new module via Visual Basic from Notepad. I would then have to open Macros and put the shortcut for each macro via the Options i.e. Ctrl+o would run the Sub Order() etc.
Then I could go to the wine order worksheet and run the Sub Summary() macro - to put the date into cell A1 and "SUMMARY SHEET" into cell B2. Then I would run the Sub Rename() to rename that main worksheet so that it could be identified later by Excel.
As I have been told there could be anything up to 90 columns of orders for each customer on this sheet, Sub Order() consisted of copying the whole row for one client from the SUMMARY SHEET into row 3 of the inserted worksheet.
Then on that new worksheet I copied columns F3 through to DZ3 (all the wine orders for that client) and then special Transpose pasted onto F4 to F104 (which should actually have been row F94 coming to think of it). I then deleted F3 to DZ3 and Shift cells up.
I could then click on F3 select the whole column with Ctrl+shift+down and do the Text to Columns with "|" as the Other delimiter and put the Destination as $F$3.
I then put all the headings onto Row 2 with the Customer Full Name in cell B2 and resized the columns then went back to the next row to be copied in the SUMMARY SHEET.
When all the client orders were put into new worksheets, I would then run the Sub Rename() macro and the new worksheets would then be renamed after the client name in cell B2 of each worksheet.
Phew!
Ideally, you would attach the "original" text file before converting to excel.
Of course, the data must be crafted so that it is not true, it's about the layout of the data itself in a text file.
Do you have a sample text file of the order, let's say the 'original' file that you start with?
The example is the "Wine List to put into order" on the first page of this thread - it usually comes through as a .csv file
It's already a converted form of file, a converted csv file, not a raw one.
This is not about the original file, but about file corresponding to the truth, with the changed data.
Okay, use a converted csv to xlsx file
The actual processing file is zipped because xlam files are not allowed
I have attached a file named Wine Order processing.zip which contains a file named Wine Order processing.xlam notice the xlam extension, it works like an Add-In but you do not need to specifically install it. Just place it in any folder and double click to open it.
You won't see any different when its opened but it is loaded into memory I changed the macro shortcut key to Ctll+w because Ctrl+o is normally use to open a file
There are two modules in the file, one named OrderProcessingModule and one named XIFileBrowseAndSelectmod this second one contains some general purpose UDFs for the extra functionality
When you press Ctrl+w the first question will be to select where you want the target file(s) to be saved, can be any file location, this will be saved and used as default and you will be prompted if you want to use this location pressing No will bring you to the option to select another location.
The next step is to point out where the wine order file is located
Then it's opened an processed. The result is attached here too, name for the resulting file is Wine-20180516-2348.xlsx the time stamp is yyyymmdd-hhmm year month day hour minute
I have attached the wine order file you posted in the first place
Give it a run and I'm curious to hear your findings.
Good morning, don't know if you've had the chance to look at it.
I modified the xlam and added two ribbon buttons so you don't have to always use the macro shortcut.
The macro shortcut will still work but I find buttons easier to use and now you also have the option to only unload the Wine order module from memory
Just a question: have you considered creating a file for each customer? eg. Wine-20180517-0933-John Doe.xlsx and Wine-20180517-0932-Jane Smith.xlsx ?
It's a minor modification in the code, you could even add the option create one file per order or create individual files per order.
VBA can be fun
Last one, promise
Added a user form when you use the ribbon button
The zip file contains the xlam module, the wine order file and the three outputs, one is all order i separate worksheets and the other is the result when selecting a separate file per customer
Hope I haven't scared you off too much.
This is what I do for a living as well (helps with the brain gymnastics
Enjoy ans just holler if you have questions.
Hasta luego.
The manual
One item is slightly different than the last version I uploaded, it's cosmetic
Dear Keebellah,
WOW! I cannot thank you enough! That is truly amazing - so beautifully put together and far more than I thought possible.
I am thrilled. Thank you soo much.
A very grateful and in awe vba newbie,
Angelique
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks