Move ranges from workbook to workbook based on cell value

New user trying again but simplifying my query.
I need to move 2 ranges (Datarange and Spreadsheetrange) in one workbook into two different workbooks.
Which workbook receives which range is based on user input (Sheet M16, cells A1 and A11).
I think searching destination workbook file to match user input and then adding range to end of data is logic needed.
I don't know VBA well enough to accomplish this unfortunately.

Attached is workbook with both ranges on sheet M16.

Any and all help is appreciated!

im sure its possible, but this may be a bit beyond me, but ill try my best and im sure the information I ask will help others to help you. You want to split data from sheet M16 and move it to a workbook depending on whats in A1, and A11 (so there would be 3 total workbooks for this file, the first you started, a kq034 workbook, and a kq034-a workbook. Now im assuming that the next time, you might change A1, and A11, say A1 becomes KQ091, you would want a corresponding workbook to be created or data entered into it, is that correct? Also, would this data replace whats in the workbook or just build up on each next row? And where are you getting the data to be entered from? Can you give me sample user data that they would enter as well?

Do you just intend to copy A3:C8, after the macro finishes collecting user data, and In the order that its presented on M16?

how are the job numbers figured? Are they set or are they generated on the fly as you are going

Also, each time you run the form, are we copying A12:E17 only, as in, thats the only range that will ever occur to export, and then paste into a workbook named after A10?

This is a quick example. You can test this out if you would like. Run this from that main workbook you just uploaded. Create a blank workbook and save it as the job name. I set the below to your network drive but you could create a local folder on your desktop with this workbook and the one you intend to split to and test it that way. Just change the line with the drive to match your setup correctly. Make sure that your file format below is set correctly (xls or xlsx)! This however, will not create a new workbook, yet, so if you want to test it, make a blank workbook. You can step through it to see what it does by using "F8" in the vba editor

Please Login or Register  to view this content.

Things to do yet:

- Check if workbook exists, if not, then create workbook in the network drive directory, and paste the data
- apply the code to cell A20 to do the same thing as above
- Clear out your data

I think you would run this macro after running your user form, and you can chain them together. Once you change the status of the opened excel to invisible, it should appear to seamlessly insert your data into a new file. I will work on the rest of this tomorrow for you, assuming someone else doesn't beat me to it

Ok sorry for late reply. I am newer to VBA so forgive me, the code is a bit repetitive (couldve subbed out the jobid with destsheet variables), but with screenupdating off it shouldn't matter. Run this in the sheet, or check out the sub ExportUserForm in the attached sheet.

Please Login or Register  to view this content.

Run this a few times with different names in A10/A20 and then run it some more and test it out. It is set to create .xlsx files, assuming you support that, if not you will need to change the file extensions to .xls. Im pretty sure I matched all your drives up. Be Aware, that if you have illegal characters in A10/A20(characters you normally can't save with), the file will not save, and it will bug out. If you hit debug on this, give me the lines and the reason, this macro is dependent upon your network drive being "Ready", keep that in mind

Manualinput.xlsm