Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Mutiple Data Movement Actions between Excel and Word including Mail Merge

speed88bumpspeed88bump Posts: 1Member

I do not know if you can help me or not. I have been working endlessly on a code. I have worked it out in bits and pieces and some pieces I have no clue even how to do. Rather than post the code in its current form and confuse everyone I posted what I would like to accomplish. If you are able to create it, I can then place them side by side and highlight my errors. I am newbie teaching myself code currently. This one really takes the cake though. It is tough.

Project Data Workbook Automation

Excel Active Worksheet – Save as “Temp Packing List” to L:\Logistics Services\Kitting\Kitting Templates – Select Cells A8:F8 down to last Cell with Data – Copy Selection

Excel Open Workbook L:\Logistics Services\Kitting\Kitting Templates ”Shortage Sheet Template.xlsx”

Select Cell B7 and Paste

Go to: Temp Packing List – Select Cell Range A1:I6 – Unmerge – IF Cell I1 is blank, select Cell F1 Cut and Paste to I1, If Not then Select I1:I4 Copy Selection

Go to: Shortage Sheet Template – Select Cell Range E1:E4 Paste Selection

Go to: Temp Packing List – Select Cell Range C1:C4 Copy Selection Go to: Shortage Sheet Template – Select Cell Range C1:C4 Paste Selection – Select Cell Range C1:D1 Merge & Center, Select Cell Range C2:D2 Merge & Center, Select Cell Range C3:D3 Merge & Center, Select Cell Range C4:D4 Merge & Center.

Pop Up User Entry Box Statement “Enter Kit Qty Here” (User Inputs a Number, that number is then pasted into Cell G1)

Next Statement “Select Lead from List” List: Sally, Joe, Fred, Tom, ****, Hairy (User selects a Name from list and it is pasted into Cell K4).

Next Statement “Input Release Number Here” (User Inputs a Number, that number is then pasted into Cell C5 – Select Cell C5:D5 Merge & Center).

Next Statement “Input Work Order Number Here” (User Inputs General Format, No more than 4 Characters, that entry is pasted to Cell E5).

End With – Save Workbook As L:\IO\CAPACITY RESOURCE MANAGEMENT\PPC\Kitting Schedules\Packing Lists “Use entry from Cell C5 and Cell E5 Combined to create the File Name” Ex. Cell C5 = 0123-1SIL & Cell E5 = 0004 – The File would be named “0123-1SIL WO# 0004”

Share Workbook

Select Cell Range B7:G7 to last Cell Row. – Copy Range

Open Workbook L:\Logistics Services\Kitting\Label Templates “Bulk Label Transfer 1.xlsx” – Select Cell B2 and Paste

Go to: The recently new named file that is still open. – “Use entry from Cell C5 and Cell E5 Combined to Create an Entry in Cell A2 of “Bulk Label Transfer 1.xlsx” Ex. Cell C5 = 0123-1SIL & Cell E5 = 0004 – The Cell Entry would be “0123-1SIL WO# 0004”. With “Bulk Label Transfer 1.xlsx” open Copy Cell A2 and paste until it reaches the last entry in Column B. Select Cell H2 enter “H92254-09-D-0001-XXXX“ X = Copy the first four numbers in column A and enter in its place. - Select Cell Range H2:J2 and Paste until it reaches the last entry in Column G. – Save “Bulk Label Transfer 1.xlsx”

Open Word Document Located at L:\Logistics Services\Kitting\Label Templates “Bulk Label Transfer 2.docx” Opening This Document will run the following SQL Command: SELECT * FROM Sheet1$ Data from your database will be placed in the document. Do you want to continue? Select Yes

“I have already linked and nested in the data in Microsoft Word”

Pop up Box User Entry: “How Many Kits?” (User enters a Number which will tell the printer how many copies to print).

Select Mail Merge Entry Mode, Select Finish and Merge,

Print Using Printer Name: Select printer located in Drop Down Menu called “5D Kitting Zebra ZM400 2x1”

Print Function: Number of Copies Box – (Enter the number user entered in the pop up user entry box).

Execute Print Function – IF Error Box’s pop up Select “Yes”.

End With Save File and Close

Open Workbook Location: L:\IO\CAPACITY RESOURCE MANAGEMENT\Scheduling\SCHEDULE “Master Backshop Schedule.xlm” – Search in Column AF for Matching Workbook Name of Files located in location L:\IO\CAPACITY RESOURCE MANAGEMENT\PPC\Kitting Schedules\Packing Lists, IF a Match is found. Open Matching Workbook – Select Cell G2 Copy – Paste Link in “Master Backshop Schedule.xlm” in Cell X of the previous search and match row.

Go back to Matching Workbook and Select Cell I1 Copy – Paste Link in “Master Backshop Schedule.xlm” in Cell W of the previous search and match row.

Go back to Matching Workbook and Select Cell K3 Copy – Paste Link in “Master Backshop Schedule.xlm” in Cell U of the previous search and match row.

Go back to Matching Workbook and IF Cell K2 has a Date input into the Cell then Copy – Paste Link in “Master Backshop Schedule.xlm” in Cell AC of the previous search and matched row. IF Not, then Ignore and move to next. (Since this entry is a final date entry, if possible when this date entry is made can all of the previous paste links be made as one last Paste Link and hard code data input to not be updated when Macro is processed in the future).

Save and Close “Master Backshop Schedule.xlm” IF message box pops up saying it has been updated by other users, Click OK.

Go to Matching Work Book Save and Close IF message box pops up saying it has been updated by other users, Click OK.

Sign In or Register to comment.