Using the find In VBA

Here is the link to the wetransfer documents https://we.tl/t-7d1ZrUWicC

Sorry this was so long. I wanted to make sure you understood exactly what my overall goal was.

Everything is fine, without your detailed explanation nobody outside of your company would understand it.

There are a few problems to be clarified and unfortunately I have to say in advance that IMHO it will not work in this way with the existing data.

Okay, let's start with an Invoice, I tried to enter "B070-31" in the Invoice file, but your file wont let me. A short look, problem solved, here's the data I entered:

Problem a)

How should the Invoice file know that "B070-31 Dresser" can be found in "Ashley Bedroom.xlsm" and not in "Ashley Stationary.xlsm"?

I can guess that every manufacturer has it own file, but how they are named?

Maybe there's an "Kitchen Ashley.xlsm" file anywhere? In which file is the item "B123-99 Knife"?

That can not be automated by a code.

Problem b) If we take a look into "Ashley Bedroom.xlsm" we can find

That would mean the code has to split "B070-31 Dresser" by "-" to get the Series and by " " to get the SKU and skip the remaining part "Dresser". Possible but what do we get for the next item "Q H/O Rails"?

There is no "-" in there, okay we can use any symbol as delimiter and that leads to "Q" as Series and "H" as SKU... i bet that can't be found in file "Albion Whatever.xlsm". Am I right?

Problem c)

Let us take a look into "Washington Brothers.xlsm" and compare the headings in row 5 with "Ashley Bedroom.xlsm"

They are no the same, especially there is no "Stock" in there, I can guess that "Warehouse" should mean the same...

Automation means that a process is always the same, but this only works if the data structure is always the same. And you don't have that in any way, sorry.

It all looks similar enough for a human and if I would know your job I can work with your files, no question. But VBA is like a blind man, it can't see anything.

In order to establish a secure process here, the data would have to be restructured in such a way that it is the same in all files. That means days or weeks of work. And then you would still have to write and test all the code.

All this is my opinion, let's see what Jeovany has to say about it.

Andreas.

Even if I can only automate some of the process it will save us a large amount of time in the long run.

Here is the link to the wetransfer documents https://we.tl/t-7d1ZrUWicC

I have included 3 samples of the inventory files, a copy of the invoice template, and a copy of the test ledger template (this file does not have all the needed dealers in it because I was just testing it out to make sure I could do what I wanted to do before I built the entire thing.

You asked for more details of our current data entry process. I will try to give you a run down of our current process.

We start by receiving a truck load of merchandise. We will take the packing slip and enter the quantities into the correct inventory file under the stock field. For example say we got in 4 b070-31's. I would open the Ashley Bedroom File and change the stock field of the B070-31 line, Field "F6" from 1 to 5.

Next we will field calls from dealers who are checking stock and putting in orders. If they wanted one of the B070-31's I would open the Ashley bedroom file, or whatever inventory file was correct, check the available field which is set to auto populate based on the stock field and the on hold fields from column M to XFD. If there is one available I would go to the first available hold column and enter the Dealer, Order Date, estimated pickup date and quantities that they want on order which will take that quantity out of the available column.

When the dealer comes in on the pickup date we would hand write the below invoice, price it and then mark in how much the dealer paid. After it is loaded we would take that invoice and go into the correct inventory file and manually remove the quantities that were purchased from the stock field. Then look to see if the dealer had that item on hold in columns M to XDF and if they got the entire order we would delete that column. If they only received part of the order we would change the quantities on order accordingly.

Next I would manually enter the invoice #, Date, Credit (paid) amount, Debit (Charged) Amount into our ledger book. This is what I am trying to automate with the "Test Ledger" file.

Next I would enter the date, dealer name, payment type (cash, check, or charge), and payment amount into what we call our sales book. This is where we track our cash and check deposits and our credit card amounts that we use to keep our accounts balanced. I have not worked on creating this file or macro yet.

After all this is done I would file the paper invoice in our file drawer. (this would be the saving a PDF version of the file that you talked about earlier. )

That is the process that I am trying to automate as much as possible. I am slowly building the macros separately that I would then try to combine into a single complete button. But I will also need a button that will print a load copy that doesn't complete they invoice so we can hold it until it is loaded.

Sorry this was so long. I wanted to make sure you understood exactly what my overall goal was.

Dear Tim

Re, "... The only problem is that I have 40 files because if one person is in one file adjusting it then I need other people to be able to go in and make adjustments to other files. I split it up by manufacturer and department because of that. It has crossed my mind that there may be a way to pull create a file that pulls information from a single inventory file, but I haven't gotten that far."

YES, I'm very sure it is possible,

To further help you, we need

1. A sample copy of some of the 40-plus Inventory files (the Ashley Bedroom file included)
2. A copy of the Invoice Template and Data Output Table
3. Provide if possible, more details of your current data entry process.

Re, "... I can attach some of the files I have created to show the work I am doing, but again I am drawing a blank on how to do that."

I'm afraid you missed the video I posted in my previous reply, on how to share files and folders using WETRANSFER

Please, try again

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

For other options

Click this link

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Regards

Jeovany

Sorry I misread some of your message. Where would I need to follow you and what does that entail? Doing the work doesn't bother me. What I am mainly trying to do is automate as much of our process as possible, but changing as little of the steps as possible. Yes I know that we will be moving from pen and paper to computer based, but I feel that if I do it right we can still follow mostly the same process that we currently do.