First of all, I'm essentially a VB novice - I've copied and applied a few very basic scripts, but nothing of this magnitude, so any and all help is greatly appreciated. If I am unclear or don't give enough information, please let me know. Thank you so much for being willing to help.
Here we go.
I am a wholesaler and we have many retail customers. More and more, our customers are requiring us to send them a file on a daily basis that informs them of the we have on hand of only the items we sell them. I am able to download an inventory file from our warehouse. Each day, when I download the file, I save it as "today.xlsx". I have a second file, that I call "inventory template" that I then open once the appropriate "today" file has been downloaded and opened. The "inventory template" is laid out something like this:
A B C(blank) D E F(blank) G H
Customer 1 Customer 2 Customer 3
Item 1 Qty Item 2 Qty Item 3 Qty
Item 3 Qty Item 1 Qty Item 4 Qty
And so on. The 'inventory template' file uses vlookups to pull the appropriate quantity for each of the items per customer. Now, we're using a system that will allow us to upload a file to transmit the quantities to each customer. The file must be a very specific format, and contains more information that item number and quantity. So, I'd need the macro to open a file, let's say "Customer 1.xlsx", Copy the items in Column A from A2 down until the end into Column F of "Customer 1" and then Copy B2 down into Column I of "Customer 1. Same thing for a file called "Customer 2" and columns D & E, Customer 3, columns G&H.
If it matters, the Customer 1, Customer 2 and Customer 3 Files will contain "Today" formulas to insert the current date in each row.
In a perfect world, the macro would then save the file "Customer 1" with the new data in it as "Customer1-mmddyyyy.csv", and so on with Customer 2, Customer 3. Is this even possible?
I can't imagine how complicated this is and truly appreciate any and all feedback I can get. Thank you in advance so much for your time.
Macro to copy column to existing workbook and save as