SOLVED

VBA code

%3CLINGO-SUB%20id%3D%22lingo-sub-2283009%22%20slang%3D%22en-US%22%3EVBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283009%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20sheet%20(attached)that%20generates%20delivery%20reports%20based%20on%20the%20Delivery%20nr.%20entered.%26nbsp%3B%20The%20information%20on%20the%20delivery%20note%20is%20pulled%20from%20a%20source%20table%20that%20has%20all%20the%20data.%20After%20entering%20a%20delivery%20note%20nr.%20in%20cell%20E7%2C%20I%20have%20to%20%22Select%20All%22%20in%20the%20description%20column%20then%20deselect%20the%20hyphen%20so%20as%20to%20have%20a%20neat%20report%20without%20the%20blank%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20assisted%20with%3B%3C%2FP%3E%3CP%3E1.%20a%20code%20that%20will%20do%20above%20in%20one%20click.%20If%20possible%2C%20after%20%22selecting%20all%20and%20deselecting%20the%20hyphen%2C%20the%20code%20to%20print%20preview.%3C%2FP%3E%3CP%3E2.%20Is%20there%20a%20possibility%20of%20generating%20multiple%20delivery%20reports%20from%20an%20excel%20table%20with%20data.At%20the%20moment%2C%20I%20can%20only%20enter%20one%20delivery%20note%20number%20at%20time.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2283009%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283971%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283971%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40A_SIRAT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20example%20that%20may%20give%20you%20some%20ideas.%20I%20added%20a%20worksheet%20with%20a%20structured%20table%20to%20list%20the%20delivery%20numbers%20to%20include%20in%20a%20%22batch%20processing%22%20function.%20The%20filter%20and%20print%20preview%20is%20triggered%20by%20the%20worksheet%20change%20event%20handler.%20Then%2C%20there%20is%20a%20button%20that%20triggers%20the%20batch%20processing%2C%20which%20will%20get%20the%20delivery%20numbers%20from%20the%20table%20on%20Sheet2%2C%20then%20pass%20them%20to%20the%20Delivery%20Note%20worksheet%2C%20which%20will%20change%20the%20value%20in%20the%20delivery%20number%20cell%20and%20trigger%20the%20change%20event%20handler.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20you%20should%20be%20able%20to%20input%20a%20delivery%20number%20yourself%2C%20or%20run%20a%20batch.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286497%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286497%22%20slang%3D%22en-US%22%3EThank%20you%20JMB17%20!%20This%20has%20really%20simplified%20my%20work.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286802%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40A_SIRAT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20JMB17%2C%3C%2FP%3E%3CP%3EI%20modified%20the%20excel%20file%20to%20reflect%20what%20I%20really%20want%20in%20your%20original%20file%20and%20it%20worked%20perfectly.%20However%2C%20after%20coping%20the%20code%20to%20my%20other%20workbook%2C%20I%20got%20below%20error.%20I%20am%20not%20good%20with%20VBA%20but%20I%20think%20the%20sheet%20names%20could%20be%20bringing%20the%20error.%20Please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_SIRAT_0-1619292455308.png%22%20style%3D%22width%3A%20620px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275292iFF4C5C90F8F4CF39%2Fimage-dimensions%2F620x386%3Fv%3Dv2%22%20width%3D%22620%22%20height%3D%22386%22%20role%3D%22button%22%20title%3D%22A_SIRAT_0-1619292455308.png%22%20alt%3D%22A_SIRAT_0-1619292455308.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_SIRAT_1-1619292590384.png%22%20style%3D%22width%3A%20693px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275293i3FAE2A8128180AA5%2Fimage-dimensions%2F693x497%3Fv%3Dv2%22%20width%3D%22693%22%20height%3D%22497%22%20role%3D%22button%22%20title%3D%22A_SIRAT_1-1619292590384.png%22%20alt%3D%22A_SIRAT_1-1619292590384.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286874%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40A_SIRAT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20the%20sheet%20name%20will%20need%20to%20be%20changed.%20My%20code%20is%20using%20the%20worksheet%20code%20name%2C%20which%20may%20or%20may%20not%20be%20the%20same%20as%20the%20worksheet%20tab%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20open%20the%20vba%20project%20explorer%20(view%2Fproject%20explorer)%20and%20expand%20the%20%22Microsoft%20Excel%20Objects%22%20under%20your%20workbook%2C%20you%20will%20see%20your%20worksheets%20listed.%20Each%20worksheet%20has%20two%20names%20-%20the%20first%20name%20is%20the%20%22code%20name%22%20and%20the%20second%20is%20the%20worksheet%20tab%20name%20(the%20one%20in%20parentheses).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1619293840025.png%22%20style%3D%22width%3A%20459px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275294i908DF1A98B47CECF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1619293840025.png%22%20alt%3D%22JMB17_0-1619293840025.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20replace%20Sheet1%20with%20the%20code%20name%20that%20actually%20contains%20your%20list%20of%20delivery%20numbers%20and%20the%20%22GetDeliveryNumbers%22%20function.%20Alternatively%2C%20you%20could%20reference%20the%20worksheet%20using%20the%20tab%20name%20with%20Worksheets(%22TabNameHere%22).GetDeliveryNumbers%20(but%20then%20it%20would%20fail%20if%20the%20tab%20name%20is%20ever%20changed%20-%20using%20the%20worksheet%20code%20name%20is%20generally%20more%20reliable).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2288306%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2288306%22%20slang%3D%22en-US%22%3EThank%20you%20!%3C%2FLINGO-BODY%3E
Contributor

Hi,

I have an excel sheet (attached)that generates delivery reports based on the Delivery nr. entered.  The information on the delivery note is pulled from a source table that has all the data. After entering a delivery note nr. in cell E7, I have to "Select All" in the description column then deselect the hyphen so as to have a neat report without the blank rows.

 

I would like to be assisted with;

1. a code that will do above in one click. If possible, after "selecting all and deselecting the hyphen, the code to print preview.

2. Is there a possibility of generating multiple delivery reports from an excel table with data.At the moment, I can only enter one delivery note number at time.

Thanks.

5 Replies
best response confirmed by A_SIRAT (Contributor)
Solution

@A_SIRAT 

 

I attached an example that may give you some ideas. I added a worksheet with a structured table to list the delivery numbers to include in a "batch processing" function. The filter and print preview is triggered by the worksheet change event handler. Then, there is a button that triggers the batch processing, which will get the delivery numbers from the table on Sheet2, then pass them to the Delivery Note worksheet, which will change the value in the delivery number cell and trigger the change event handler.

 

So, you should be able to input a delivery number yourself, or run a batch.

 

 

Thank you JMB17 ! This has really simplified my work.

@A_SIRAT 

 

Hi JMB17,

I modified the excel file to reflect what I really want in your original file and it worked perfectly. However, after coping the code to my other workbook, I got below error. I am not good with VBA but I think the sheet names could be bringing the error. Please assist.

 

A_SIRAT_0-1619292455308.png

 

A_SIRAT_1-1619292590384.png

 

@A_SIRAT 

 

Yes, the sheet name will need to be changed. My code is using the worksheet code name, which may or may not be the same as the worksheet tab name.

 

If you open the vba project explorer (view/project explorer) and expand the "Microsoft Excel Objects" under your workbook, you will see your worksheets listed. Each worksheet has two names - the first name is the "code name" and the second is the worksheet tab name (the one in parentheses).

JMB17_0-1619293840025.png

 

 

You will need to replace Sheet1 with the code name that actually contains your list of delivery numbers and the "GetDeliveryNumbers" function. Alternatively, you could reference the worksheet using the tab name with Worksheets("TabNameHere").GetDeliveryNumbers (but then it would fail if the tab name is ever changed - using the worksheet code name is generally more reliable).

 

Thank you !